How many constraints can be given to a column of a table in SQL Server

Question

Hi,

Can anyone explain me how many constraints at most can be set on any given column of a table in SQL Server.

Answer 1

Constraints:

• Primary key
• NULL/NOT NULL
• DEFAULT
• UNIQUE (likely conflict with DEFAULT)
• Foreign Key (unlimited that I'm aware of, not that you want more than one typically)
• CHECK (also unlimited to my knowledge, but extremely odd to see more than one)

Answer 2

Don't think there is a practical limit, I just created a table with more than 9k constraints:

create table #TheresNoLimit (
    id int,
    constraint constr1 check (id = 1),
    constraint constr2 check (id = 1),
    constraint constr3 check (id = 1),
    ...
    constraint constr9985 check (id = 1)
)

Answer 3

"It depends"

• Primary key (maximum one if clustered, or part of max nonclustered indexes = 999 on SQL Server 2008)
• DEFAULT (maximum one per column)
• NULL/NOT NULL (one or the other per column). Yes, this is a constraint
• CHECK (column or table level: lots)
• UNIQUE (part of max nonclustered indexes = 999 on SQL Server 2008, 1 if clustered)
• FOREIGN KEY (max 253)

Notes:

• things like one IDENTITY or timestamp/rowversion or ROWGUIDCOL per table
• you have a max of 1024 columns per standard table which obvious limits number of defaults
• some will be mutually exclusive

Some taken from here: Maximum Capacity Specifications for SQL Server