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.