SQL Server Error Msg 142 – Incorrect syntax for definition of the ‘%ls’ constraint

In this blog post, let’s learn about the error message “142 – Incorrect syntax for definition of the ‘%ls’ constraint.” in Microsoft SQL Server, the reason why it appears and the solution to fix it.

SQL Server Error Message

142 – Incorrect syntax for definition of the ‘%ls’ constraint.

Reason for the Error

SQL Server Error Msg 142 is raised when there is an error in the syntax of a constraint definition. This error occurs when there is an incorrect or missing keyword, or when a keyword is used in the wrong context.

Consider the following CREATE TABLE statement:

CREATE TABLE PROJECT (
  ProjectID   INT NOT NULL IDENTITY(1000, 100),
  ProjectName CHAR(50) NOT NULL,
  Department  CHAR(35) NOT NULL,
  MaxHours    NUMERIC(8,2) NOT NULL, DEFAULT 100
);

This statement attempts to create a new table called PROJECT with four columns: ProjectID, ProjectName, Department, and MaxHours. The ProjectID column is defined as an IDENTITY column with a starting value of 1000 and an increment of 100. The ProjectName, Department, and MaxHours columns are defined as CHAR, CHAR, and NUMERIC data types, respectively.

However, there is an error in the MaxHours column definition. The keyword DEFAULT is followed by a comma, which causes SQL Server to interpret DEFAULT as a new column definition rather than a constraint definition. This results in SQL Server Error Msg 142: “Incorrect syntax for definition of the ‘MaxHours’ constraint”.

Solution

To fix SQL Server Error Msg 142, you need to remove the comma before the DEFAULT keyword in the column definition. Here’s the corrected CREATE TABLE statement:

CREATE TABLE PROJECT (
  ProjectID   INT NOT NULL IDENTITY(1000, 100),
  ProjectName CHAR(50) NOT NULL,
  Department  CHAR(35) NOT NULL,
  MaxHours    NUMERIC(8,2) NOT NULL DEFAULT 100
);

In this corrected statement, the MaxHours column is defined with the DEFAULT constraint after the NOT NULL constraint. This specifies that the MaxHours column will have a default value of 100 if no value is provided for the column when a new row is inserted into the table.

By removing the comma before the DEFAULT keyword, we have corrected the syntax of the constraint definition and eliminated SQL Server Error Msg 142.