An trivial problem came to my desk recently. We were having some issues in creating a table. The script was quite simple, and yet we were facing errors as shown below.

USE tempdb;
GO
IF OBJECT_ID('dbo.ConstraintsCheck','U') IS NOT NULL
    DROP TABLE dbo.ConstraintsCheck;
GO

CREATE TABLE dbo.ConstraintsCheck 
    (RecordId INT NOT NULL IDENTITY(1,1),
     Field1   INT NOT NULL,
     Field2   INT NOT NULL
     CONSTRAINT chk_IsField2GreaterThanField1 CHECK (Field2 > Field1)
    );
GO

The script was being run via an installer, and hence all we got was the last part of the error message:

Msg 1750, Level 16, State 0, Line 7
Could not create constraint or index. See previous errors.

If you have already caught the error, great work! As for us, it took a couple of minutes and running the script via SSMS before we realized that the issue was a just a plain human error.

Here’s the full error that we got when the script was executed in SSMS:

Msg 8141, Level 16, State 0, Line 7
Column CHECK constraint for column 'Field2' references another column, table 'ConstraintsCheck'.
Msg 1750, Level 16, State 0, Line 7
Could not create constraint or index. See previous errors.

The first message that is thrown is the key – it clearly tells us that the CHECK constraint definition cannot be created because it references another column. However, this is a fairly common requirement which is what threw us off.

Finally we realized that we did not have a comma in the T-SQL script before the constraint was defined. Without the comma, SQL Server is trying to create a column constraint, when what we wanted was a table constraint. Here’s the extract from TechNet:

• A column constraint is specified as part of a column definition and applies only to that column.
• A table constraint is declared independently from a column definition and can apply to more than one column in a table.

So, we just added the comma to convert the column constraint to a table constraint and we were all set.

USE tempdb;
GO
IF OBJECT_ID('dbo.ConstraintsCheck','U') IS NOT NULL
    DROP TABLE dbo.ConstraintsCheck;
GO

CREATE TABLE dbo.ConstraintsCheck 
    (RecordId INT NOT NULL IDENTITY(1,1),
     Field1   INT NOT NULL,
     Field2   INT NOT NULL, --<-- A comma here makes it a legal Table Constraint
     CONSTRAINT chk_IsField2GreaterThanField1 CHECK (Field2 > Field1)
    );
GO

References:

• CONSTRAINT Documentation [TechNet Link]

Until we meet next time,

Be courteous. Drive responsibly.