SQL Server – Difference Between Key and Constraint

sql serverterminologyunique-constraint

In SQL Server, the 'keys' folder includes primary, foreign and unique keys, while the 'constraints' folder, for example, check and default. But unique can also be considered a constraint, so why it is kept under the 'keys' folder in SQL Server? Is it because it represents alternate keys in logical modeling?

I want to know the strict definition of constraint and key which I cannot find. Thanks.

Best Answer

A unique constraint and a unique index are physically implemented the same way and have the same end result. Since a unique constraint is actually physically enforced via a unique index, and since both can impersonate a primary key in terms of relationships with other tables, this is why you will see it under "keys" - even if you say ADD CONSTRAINT you've still created a constraint that acts and smells like a key.

What people tend to do is create them based on the logical thing that represents their intention. The typical guidance I see is as follows:

  • Is it being created primarily to enforce uniqueness? Then it's a constraint.
  • Is it being created primarily to improve performance of certain queries or operators? Then it's an index.

These aren't hard and fast rules, and of course you may want to use different rules. The salient part of these rules, though, is not where these things appear in Object Explorer, but rather what the script says about the intent (ADD CONSTRAINT ... UNIQUE vs. CREATE UNIQUE ... INDEX).

"Constraint" and "key" as words themselves overlap, but they are not interchangeable. A NOT NULL or DEFAULT or CHECK constraint does not relate at all to a key, and in fact two of those effectively violate the definition of a key. On the flip side, any type of key you can create fit the definition of a constraint, since that word is far less specific.

Related Question