Error Message:
Msg 8148, Level 16, State 0, Line 1
More than one column %1! constraint specified for column ‘%2!’, table ‘t’.
Severity level:
16.
Description:
This error message appears when you try within a CREATE TABLE statement to create more than one constraint for one column for which already another constraint of the same kind was declared in that statement.
Consequences:
The T-SQL statement can be parsed, but causes the error at runtime.
Resolution:
Error of the Severity level 16 are generated by the user and can be fixed by the SQL Server user. For each column in a CREATE TABLE statement there can only be one constraint of the same kind.
Versions:
All versions of SQL Server.
Example(s):
CREATE TABLE t
(
c1 INT DEFAULT 0
CONSTRAINT DF_1 DEFAULT 1
)
GO
DROP TABLE t
CREATE TABLE t
(
c1 INT
CONSTRAINT CK_1 CHECK(c1 > 0)
CONSTRAINT CK_2 CHECK(c1 < 0)
)
GO
DROP TABLE t
Remarks:
It is not possible to create more than 1 DEFAULT constraint for a single column. No matter if you try to do so directly in the CREATE TABLE statement or afterwards via ALTER TABLE. However, if you try to do so in an ALTER TABLE statement, SQL Server raises error 1781.
However it is very well possible to have more than one CHECK constraint on a single column. Though not directly via the CREATE TABLE statement, but rather at a later point in time via an ALTER TABLE statement:
CREATE TABLE t
(
c1 INT
CONSTRAINT CK_1 CHECK(c1 > 0)
)
GO
ALTER TABLE t
ADD CONSTRAINT CK_2 CHECK(c1 < 10)
DROP TABLE t
]]>