Five types of constraints on SQL Server data tables

1. PRIMARY KEY constraint (PRIMARY key)

A PRIMARY KEY constraint can define a primary key value in a table that uniquely identifies each record in the table and can have only one PRIMARY KEY constraint per table (only one PRIMARY KEY constraint does not mean that there can be only one column for a primary KEY constraint), and a column that is constrained by a primary key cannot be a null value. If a primary key constraint is defined on more than one column, the values in one column can be duplicated, but the combined values of all columns that are constrained by the primary key must be unique.

2, Uniqueness constraint (UNIQE)

Uniqueness constraints ensure that duplicate values are not entered in a non-primary key column. Used to specify that a combination of one or more columns is unique to prevent duplicate values from being entered in the column. You can define multiple uniqueness constraints in one table, but only one primary KEY constraint. A uniqueness constraint allows null values, but when used with any value that participates in a uniqueness constraint, only one null value is allowed per column.

3, check the constraints

The check constraint sets the check condition for values in the input column or the entire table, and can limit the input values to ensure the integrity of the database. The check constraint determines the valid value through the logical expression of the data. For example, to define an age ages field, you can limit the range of values inside the age field to between 0 and 150 by creating a check constraint (>=0 and age<=150).

• Only one check constraint can be set per field.
• The check constraint cannot contain subqueries.
• A column-level CHECK constraint can only be related to a restricted field, and a table-level CHECK constraint can only be related to a field in a restricted table.

4. Default Constraints

The default constraint specifies that when an input value is not provided in an insert operation, the system automatically formulates the insertion value, even if the value is null. You can use a default value constraint when you must load a row of data into a table, but you do not know the value of a column, or if the value does not already exist.

5, FOREIGN KEY constraints

Original Address: http://www.phpxs.com/post/3154

Five types of constraints on SQL Server data tables