Create and use Constraints

Constraint is a method provided by Microsoft SQL Server to automatically maintain database integrity, defines the conditions for data in a single column of an input table or table (for details about data integrity, see Chapter 9th ). There are five constraints in SQL Server: Primary Key Constraint, Foreign Key Constraint, Unique Constraint, and Check Constraint) and Default constraints (Default Constraint ).

7.2.1 primary keyword Constraints
The primary keyword restricts the value of a combination of one or several columns in a table to be unique. That is, a single row of records can be specified. Only one column in each table can be specified as the primary keyword, and columns of the IMAGE and TEXT types cannot be specified as the primary keyword, or the primary key column cannot have the NULL attribute.
Syntax for defining the constraint of the primary keyword is as follows:
CONSTRAINT constraint_name
Primary key [CLUSTERED | NONCLUSTERED]
(Column_name1 [, column_name2 ,..., Column_name16])
Parameters are described as follows:

Constraint_name
Name of the constraint. It should be unique in the database. If this parameter is not specified, the system automatically generates a constraint name.
CLUSTERED | NONCLUSTERED
Specify the index category. CLUSTERED is the default value. For more information, see the next chapter.
Column_name
Specifies the name of the column that constitutes the primary keyword. A primary keyword consists of up to 16 columns.

Example 7-3: create a product information table with the product ID and name as the key words
Create table products (
P_id char (8) not null,
P_name char (10) not null,
Price money default 0.01,
Quantity smallint null,
Constraint pk_p_id primary key (p_id, p_name)
) On [primary]

7.2.2 foreign keyword Constraints
The foreign keyword constraint defines the relationship between tables. When the combination of one or more columns in a table and the definition of the primary keyword in other tables are the same, the combination of these columns or columns can be defined as a foreign keyword, set which columns are associated with the table. In this way, when the column value is updated in the table that defines the primary keyword constraint, when other tables have external keyword constraints associated with the table, the external keyword columns are also updated accordingly. The role of the external keyword constraint is also reflected in when you insert data to a table that contains external keywords, if the columns of the associated table do not have the same value as the values of the inserted foreign keyword column, the system rejects data insertion. Similar to the primary keyword, you cannot use a column defined as TEXT or IMAGE data type to create an external keyword. A foreign keyword consists of up to 16 columns.
Syntax for defining external keyword constraints:
CONSTRAINT constraint_name
Foreign key (column_name1 [, column_name2 ,..., Column_name16])
REFERENCES ref_table [(ref_column1 [, ref_column2 ,..., Ref_column16])]
[On delete {CASCADE | no action}]
[On update {CASCADE | no action}]
[Not for replication]
Parameters are described as follows:

REFERENCES
Specifies the information of the table to be joined.
Ref_table
Name of the table to be joined.
Ref_column
Name of the related columns in the table to be joined.
On delete {CASCADE | no action}
Specifies the operations performed on the associated table when the table data is deleted. When a child table has a data row associated with the corresponding data row in the parent table, if the Value CASCADE is specified, when the data row in the parent table is deleted, the corresponding data row in the child table is deleted. If no action is specified, SQL Server generates an error, and roll back the deletion operation in the parent table. No action is the default value.
On update {CASCADE | no action}
Specifies the operations performed on the associated table when updating table data. When a child table has a data row associated with the corresponding data row in the parent table, if the Value CASCADE is specified, when updating the data in the parent table, the corresponding data rows in the child table are updated. If no action is specified, SQL Server generates an error, and roll back the update operation in the parent table. No action is the default value.
NOT FOR REPLICATION
The foreign keyword constraint of the specified column does not apply when the data copied from other tables is inserted into the table.
Example 7-4: Create an order table, which is associated with the previously created product table
Create table orders (
Order_id char (8 ),
P_id char (8 ),
P_name char (10 ),
Constraint pk_order_id primary key (order_id ),
Foreign key (p_id, p_name) references products (p_id, p_name)
) On [primary]
Note: External keyword constraints cannot be specified for temporary tables.
7.2.3 uniqueness constraints
The Uniqueness constraint specifies that the value of a combination of one or more columns is unique to prevent repeated values from being input in the column. The column specified by the uniqueness constraint can have the NULL attribute. Because the primary key value is unique, you cannot set the uniqueness constraint for the primary key column. The Uniqueness constraint consists of up to 16 columns.
The syntax for defining uniqueness constraints is as follows:
CONSTRAINT constraint_name
UNIQUE [CLUSTERED | NONCLUSTERED]
(Column_name1 [, column_name2 ,..., Column_name16])

Example 7-5: Define an employee information table. The employee ID card number is unique.
Create table employees (
Emp_id char (8 ),
Emp_name char (10 ),
Emp_cardid char (18 ),
Constraint pk_emp_id primary key (emp_id ),
Constraint uk_emp_cardid unique (emp_cardid)
) On [primary]

7.2.4 check Constraints
Check constraints configure check conditions for values in the input column or the entire table to restrict the input values and ensure the data integrity of the database. You can set a compliance check for each column.
Syntax for defining check constraints:
CONSTRAINT constraint_name
CHECK [not for replication]
(Logical_expression)
Parameters are described as follows:
NOT FOR REPLICATION
Specifying check constraints does not apply when you insert data copied from other tables into the table.
Logical_expression
Specify whether the return value of the logical condition expression is TRUE or FALSE.
Example 7-6: Create an order table with the order quantity not less than 10.
Create table orders (
Order_id char (8 ),
P_id char (8 ),
P_name char (10 ),
Quantity smallint,
Constraint pk_order_id primary key (order_id ),
Constraint chk_quantity check (quantity> = 10 ),
) On [primary]
Note: The calculation Column cannot be subject to any constraints except the check constraints.

7.2.5 default Constraints
The default constraint specifies the column default value by defining the column default value or binding the table column with the database default value object. We recommend that you use the default constraints instead of defining the default values to specify the column's default values. For more information about binding default constraints, see the "Data Integrity" section.
Syntax for defining default constraints:
CONSTRAINT constraint_name
DEFAULT constant_expression [FOR column_name]
Example 7-7:
Constraint de_order_quantity default 100 for order_quantity
Note: you cannot define the default constraint when creating a table. You can only add the default constraint to a created table.

7.2.6 column and table Constraints
For databases, constraints are divided into Column Constraint and Table Constraint ).
A column constraint acts only on the column itself as a part of the column definition. Table constraints, as part of table definitions, can act on
Multiple columns.
The following example illustrates the differences between a column constraint and a table constraint.
Example 7-8:
Create table products (
P_id char (8 ),
P_name char (10 ),
Price money default 0.01,
Quantity smallint check (quantity> = 10),/* column constraint */
Constraint pk_p_id primary key (p_id, p_name)/* Table constraints */

Reference from: