IntroductionIn this article, I want to tell you about SQL Server constraints: what a constraint is, when it is used and for which purposes. You can find in this article some examples of constraint creation and SQL Server 2000 constraints enhancements.
General ConceptsA constraint is a property assigned to a column or the set of columns in a table that prevents certain types of inconsistent data values from being placed in the column(s). Constraints are used to enforce the data integrity. This ensures the accuracy and reliability of the data in the database. The following categories of the data integrity exist:
Entity Integrity ensures that there are no duplicate rows in a table.
Domain Integrity enforces valid entries for a given column by restricting the type, the format, or the range of possible values.
Referential integrity ensures that rows cannot be deleted, which are used by other records (for example, corresponding data values between tables will be vital).
User-Defined Integrity enforces some specific business rules that do not fall into entity, domain, or referential integrity categories.
Each of these categories of the data integrity can be enforced by the appropriate constraints. Microsoft SQL Server supports the following constraints:
A PRIMARY KEY constraint is a unique identifier for a row within a database table. Every table should have a primary key constraint to uniquely identify each row and only one primary key constraint can be created for each table. The primary key constraints are used to enforce entity integrity.
A UNIQUE constraint enforces the uniqueness of the values in a set of columns, so no duplicate values are entered. The unique key constraints are used to enforce entity integrity as the primary key constraints.
A FOREIGN KEY constraint prevents any actions that would destroy link between tables with the corresponding data values. A foreign key in one table points to a primary key in another table. Foreign keys prevent actions that would leave rows with foreign key values when there are no primary keys with that value. The foreign key constraints are used to enforce referential integrity.
A CHECK constraint is used to limit the values that can be placed in a column. The check constraints are used to enforce domain integrity.
A NOT NULL constraint enforces that the column will not accept null values. The not null constraints are used to enforce domain integrity, as the check constraints.
You can create constraints when the table is created, as part of the table definition by using the CREATE TABLE statement.
ExamplesThe following example creates a check_sale CHECK constraint on an employee table:
CREATE TABLE employee(
ALTER TABLE employee
ALTER TABLE employee
The following example disables the check_sale constraint in the employee table and enables this constraint later:
-- disable the check_sale constraint in the employee table
-- enable the check_sale constraint in the employee table
SQL Server 2000 Constraints EnhancementsSQL Server 2000 introduces many new features. Some of them fall into the constraints area. Now you can control the actions SQL Server 2000 takes when you attempt to update or delete a key to which existing foreign keys point. You can control it by using the new ON DELETE and ON UPDATE clauses in the REFERENCES clause of the CREATE TABLE and ALTER TABLE statements. For example, in the previous versions of SQL Server if you wanted to do a cascade delete from the referenced table when the appropriate record in the parent table is deleted, you had to create a trigger which executed on delete of the parent table, but now you can simply specify the ON DELETE clause in the REFERENCES clause.
The following example is used to create the Books and the Authors tables and create a foreign key constraint which will perform the cascade delete action, therefore, when a row in the Authors table is deleted, the corresponding rows in the Books are also deleted:
CREATE TABLE Books (