SQL Server Infernals – Circle 4: Anarchic Designers

Constraints are sometimes annoying in real life, but no society can exist without rules and regulations. The same concept is found in Database Design: no good data can exist without constraints.

What they say in Heaven

Constraints define what is acceptable in the database and what does not comply with business rules. In Heaven, where the perfect database runs smoothly, no constraint is overlooked and all the data obeys to the rules of angels:

• Every column accepts only the data it was meant for, using the appropriate data type
• Every column that requires a value has a NOT NULL constraint
• Every column that references a key in a different table has a FOREIGN KEY constraint
• Every column that must comply with a business rule has a CHECK constraint
• Every column that must be populated with a predefined value has a DEFAULT constraint
• Every table has a PRIMARY KEY constraint
• Every…