SQL Server series: Keys and Constraints

1. Types of Constraints

Constraints can be divided into 3 main categories: Entity constraints, domain constraints, and referential integrity constraints.

1.1> entity constraints

Entity constraints are all about comparing rows, and entity constraints do not care about the data in the overall column, it is only interested in a particular row.

1.2> domain constraints

A domain constraint processes one or more columns, ensuring that a particular column or set of specific columns meets specific criteria. When you insert or update a row, the domain constraint does not take into account other rows. If the value of the UnitPrice column is greater than or equal to 0, this is the domain constraint.

1.3> Referential integrity constraints

If the value of a column must match the value of another column, which may be in the same table, or more commonly in a different table, this means that referential integrity constraints are created.

2. Key constraints

Common key constraints include: PRIMARY KEY constraints, foreign key constraints, and unique constraints (also called substitution key constraints). A table can have only one primary KEY constraint, multiple foreign key constraints, and multiple unique constraints.

2.1> PRIMARY KEY constraints

The primary key is a unique identifier for each row, must contain a unique value, and cannot be null. A table can have more than one primary key, and of course it allows the table to be set without a primary key.

Reference rules for naming primary KEY constraints: Pk_[table_name]

ALTER TABLE dbo. [Product] ADD CONSTRAINT PRIMARY KEY ([ProductID])

In general, a primary key also creates a clustered index at the same time:

ALTER TABLE dbo. [Product] ADD CONSTRAINT PRIMARY KEY CLUSTERED ([ProductID])

2.2> FOREIGN KEY constraint

Foreign keys ensure data integrity and can also represent relationships between tables. After the foreign key is added, the record that inserts the reference table must either match a record in the referenced table column, or the value of the foreign key column is set to NULL.

FOREIGN KEY constraint naming can be referred to as a rule: Fk_[Foreign_key_table_name]_[Primary_key_table_name]

　　If there are multiple foreign key fields in the foreign key table that reference the same primary key table, the name of the foreign KEY constraint can be referred to as a rule: Fk_[Foreign_key_table_name]_[primary_key_table_name]_[ Colunm_name]

ALTER TABLE ADD CONSTRAINT FOREIGN KEY     (        CategoryID    )    REFERENCES        dbo. Category    (    CategoryID    )    onDELETE  CASCADE

SQL Server series: Keys and Constraints