Yes, it is allowed to add multiple column level CONSTRAINTs to the same column in a table.

All the CONSTRAINTs are checked for every INSERT, UPDATE operation and all must be satisfied for the INSERT/UPDATE operation to be successful.

Following is an example (DBA.SampleTable) with a column (Col2) with 3 constraints.

1. Default: DF_SampleTable_ID
2. Foreign Key : To a column in table DBA.ForeignKeyTable: FK_SampleTable_ForeignKeyTable_ID
3. Check: CK_SampleTable_ID

[sourcecode language=”sql”]
CREATE TABLE DBA.SampleTable
(
ID INT NOT NULL CONSTRAINT DF_SampleTable_ID DEFAULT (1) — First Constraint : DEFAULT
, Col2 VARCHAR(5) NOT NULL CONSTRAINT DF_SampleTable_Col2 DEFAULT ‘AA’
)
GO

CREATE TABLE DBA.ForeignKeyTable
(
ID INT NOT NULL IDENTITY(1,1) CONSTRAINT PK_ForeignKeyTable_ID PRIMARY KEY CLUSTERED
, Col2 VARCHAR(5) NOT NULL CONSTRAINT DF_ForeignKeyTable_Col2 DEFAULT ‘BB’
)
GO

— Second Constraint: CHECK
ALTER TABLE DBA.SampleTable ADD CONSTRAINT CK_SampleTable_ID CHECK (ID < 5)

— Third Constraint: Foreign Key
ALTER TABLE DBA.SampleTable ADD CONSTRAINT FK_SampleTable_ForeignKeyTable_ID FOREIGN KEY (ID) REFERENCES DBA.ForeignKeyTable(ID)
GO
[/sourcecode]

Result:

• First INSERT into DBA.SampleTable throws an error. Since there are no records in DBA.ForeignKeyTable, the constraint ‘FK_SampleTable_ForeignKeyTable_ID’ does not allow any inserts. After inserting a record into DBA.ForeignKeyTable (with ID = 1 ), inserts into DBA.SampleTable are successful.

Multiple Constraints on a Single Column Foreign Key

• But when I try to INSERT a record with ID = 6 into DBA.SampleTable, the CHECK CONSTRAINT CK_SampleTable_ID, throws error. Even after you enter more than 6 records in the second table (ID =6), no record with ID = 6 is allowed into the first table (DBA.SampleTable) as it conflicts with the CHECK CONSTRAINT (ID < 5)

Multiple Constraints on a Single Column CHECK

Hope this helps,

_SqlTimes