Constraint is a method provided by Microsoft SQL server to automatically maintain database integrity.

Constraint is a method provided by Microsoft SQL server to automatically maintain database integrity. It defines the conditions for data in a single column of an input table or table.

There are five constraints in SQL Server: primary key constraint, foreign key constraint, unique constraint, and check constraint) and default constraints (default constraint ).

1. The primary key constraint specifies the combination of one or several columns in the table. The value of the primary key constraint is unique in the table, 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 constraints of the primary Keyword: constraint constraint_name primary key [clustered | nonclustered] (column_name1 [, column_name2 ,..., Column_name16]) parameters are described as follows:

Constraint_name specifies the name of the constraint name. It should be unique in the database. If this parameter is not specified, the system automatically generates a constraint name. Clustered | nonclustered specifies 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.

For example, create a product information table with the product number and name as the primary keyword 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]

2: The External keyword constraint defines the relationship between tables. When a table contains a combination of one or more columns and

When the primary keywords in other tables are defined at the same time, you can define the combination of these columns or columns as external keywords and set them as appropriate.

Which columns are associated with the table. In this way, when the column value is updated in the table that defines the constraints of the primary keyword, other tables have the same

The external keyword columns in the table associated with the external keyword constraint are also updated accordingly. The role of the external keyword constraint is also

It is embodied in the fact that when data is inserted to a table that contains external keywords, if the columns of the table associated with the data do not have any external keywords

When the column value is the same, the system rejects data insertion. It is the same as the primary keyword and cannot be defined as text or

Create external keywords for columns of the image data type. 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 specifies the name of the table to be joined.

Ref_column specifies the names of related columns in the table to be joined. On Delete {cascade | no action} indicates

Operations related to the joined table are performed when the table data is deleted. There are data rows in the child table that correspond to the data rows in the parent table.

If the value cascade is specified for association, the corresponding data rows in the child table will be deleted when the data row in the parent table is deleted.

If no action is specified, SQL Server generates an error and rolls back the delete operation in the parent table.

No action is the default value. On update {cascade | no action} specifies

Table operations. If a child table has a data row associated with the corresponding data row in the parent table

Cascade, the corresponding data rows in the child table will be updated when the data row in the parent table is updated; if no action is specified, then s

The QL Server generates an error and rolls back the update operation in the parent table. No action is the default value. Not

The external keyword constraint of the replication specified column does not apply when the data copied from other tables is inserted into the table.

For example, create an order table and associate it 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: Foreign keyword constraints cannot be specified for temporary tables.

3. uniqueness constraint 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. Syntax for defining uniqueness constraints: constraint constraint_name unique [clustered | nonclustered] (column_name1 [, column_name2 ,..., Column_name16])

For example, define an employee information table where the employee's 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]

4: Check constraints check constraints set the check conditions for the 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 specifies that the check constraint does not apply when inserting data copied from other tables into the table. Logical_expression specifies that the return value of the logical condition expression is true or false.

For example, to create an order table, the order quantity must be no 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: you cannot impose any constraints on the computed column except the check constraint.

5 default constraints specify the column default values by defining the column default values or binding the table columns using 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.

Syntax for defining default constraints: constraint constraint_name default constant_expression [For column_name]

For example, constraint de_order_quantity default 100 for order_quantity Note: you cannot define default constraints when creating a table. You can only add default constraints to a created table.

For databases, the six-column constraints are divided into column constraints and table constraints ). A column constraint acts only on the column itself as a part of the column definition. Table constraints are part of table definitions and can act on multiple columns. The following example illustrates the differences between a column constraint and a table constraint.

example: 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 */