Wednesday, March 2, 2011

Transact SQL Constraints

1. Primary Key

Primary keys are the unique identifiers for each row. They must contain unique values. A primary key column can’t contain NULL values. A table can have a maximum of one primary key.

2. Unique Key

You can only have a single primary key defined on a table. If you wish to enforce uniqueness on other non-primary key columns, you can use a UNIQUE constraint. A unique constraint, by definition, creates an alternate key. Unlike a PRIMARY KEY constraint, you can create multiple UNIQUE constraints for a single table and are also allowed to designate a UNIQUE constraint for columns that allow NULL values.

3. Foreign Key

Foreign key constraints establish and enforce relationships between tables and help maintain referential integrity, which means that every value in the foreign key column must exist in the corresponding column for the referenced table. Each foreign key is defined using the FOREIGN KEY clause combined with the REFERENCES clause.

4. Check Constraints

The CHECK constraint is used to define what format and values are allowed for a column.

5. Default constraints

A default constraint specifies a value to be applied to a column whenever a value is not supplied in an INSERT.

No comments: