Constraint (Constraint) is a method provided by Microsoft SQL Server to automatically maintain database integrity by defining restrictions on the data that can be entered into a single column of a table or table (for an Introduction to data integrity, see chap. 9th). There are 5 kinds of constraints in SQL Server: PRIMARY KEY constraint (Primary key Constraint), foreign keyword constraint (Foreign key Constraint), uniqueness constraint (unique Constraint), check constraints Constraint) and the default constraint (defaulted Constraint).
7.2.1 PRIMARY keyword constraint
A PRIMARY KEY constraint specifies that the value of a combination of one or more columns of a table is unique in the table, that is, a single row of records can be specified uniquely. Only one column in each table can be specified as the primary key, and columns of image and text types cannot be specified as primary, or null attributes are not allowed for the specified primary key column.
The syntax for defining a PRIMARY keyword constraint is as follows:
CONSTRAINT constraint_name
PRIMARY KEY [CLUSTERED | Nonclustered]
(column_name1[, column_name2,..., Column_name16])
The
parameters are described as follows:
- Constraint_name
Specifies the name of the constraint's name constraint. Should be unique in the database. If not specified, a constraint name is automatically generated.
- CLUSTERED | Nonclustered
Specifies the index category, CLUSTERED the default value. For specific information, see the next chapter.
- column_name
Specifies the name of the column that makes up the primary key. The primary keyword consists of up to 16 columns.
Example 7-3: 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 (TEN) 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 external keyword constraint
The
keyword constraint defines the relationship between tables. When a table has a column or combination of columns and a primary keyword definition in another table, you can define a combination of those columns or columns as an external keyword and set which columns are associated with which table. In this way, when the column value is updated in the table that defines the primary KEY constraint, the foreign key column in the other table with the Foreign keyword constraint associated with it will be updated accordingly. The external keyword constraint also shows that when inserting data into a table with an external keyword, the system rejects inserting data if the table associated with it does not have the same value as the inserted external key column value. As with the primary key, you cannot create an external keyword using a column defined as the TEXT or image data type. The foreign keyword is composed of up to 16 columns.
The syntax for defining an external keyword constraint is as follows:
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]
The
parameters are described as follows:
The
- REFERENCES
Specifies the information to establish the associated table. The
- ref_table
Specifies the name of the table to establish the association. The
- ref_column
Specifies the name of the related column in the associated table to be established.
- on DELETE {CASCADE | NO Action}
Specifies the associated action to be taken on the association table when the data in the table is deleted. In cases where a row of data is associated with a corresponding row of data in the parent table, if a value cascade is specified, the corresponding row of data in the child table is deleted when the parent table row is deleted, and if no action is specified, SQL Server generates an error and rolls back the delete operation in the parent table. The NO ACTION is the default value.
- on UPDATE {CASCADE | The NO action}
Specifies the related action to be taken on the associated table when the data in the table is updated. In cases where a row of data is associated with a corresponding row of data in the parent table, if a value cascade is specified, the corresponding data row in the child table is updated when the parent table row is updated, and if no ACTION is specified, SQL Server generates an error and rolls back the update operation in the parent table. The NO ACTION is the default value. The
- not for REPLICATION
Specifies that the foreign keyword constraint for a column does not occur when data copied from another table is inserted into the table.
Example 7-4: Create an order table that is associated with the product table created earlier
CREATE table orders (
order_id char (8),
p_id char (8),
P_name char (),
Constraint pk_order_id primary KEY (order_id),
foreign key (p_id, p_name) references products (p_id, p_name)
) on [PR Imary]
Note: Temporary tables cannot specify an external keyword constraint.
7.2.3 Uniqueness Constraint
A Uniqueness constraint specifies that the value of a combination of one or more columns is unique to prevent duplicate values from being entered in the column. A Uniqueness constraint specifies a column that can have a null property. Because the primary key value is unique, the primary key column can no longer set a uniqueness constraint. A 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 sheet in which the employee's ID 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 Constraint
Check the constraint to set the check condition on the input column or the value in the entire table to restrict the input value and ensure the data integrity of the database. You can set up compliance checks for each column.
The syntax for defining CHECK constraints is as follows:
CONSTRAINT constraint_name
CHECK [Not for REPLICATION]
(Logical_Expression)
The parameters are described as follows:
Not for REPLICATION
Specifies that check constraints do not take effect when inserting data copied from other tables into the table.
Logical_Expression
Specifies that the logical conditional expression returns a value of TRUE or false.
Example 7-6: Create an order form where 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 make a computed column any constraint other than the CHECK constraint.
7.2.5 DEFAULT Constraint
The default constraint specifies the default value of a column by defining the default value of the column or by using the database's default value object to bind the table's columns. SQL Server recommends that you use default constraints instead of specifying the default values for columns by using the default values defined. For a method of binding default constraints, see the "Data integrity" section.
The syntax for defining default constraints is as follows:
CONSTRAINT constraint_name
DEFAULT constant_expression [For column_name]
Example 7-7:
Constraint de_order_quantity default for order_quantity
Note: You cannot define default constraints when creating tables, you can only add default constraints to tables that have already been created.
7.2.6 column constraints and table constraints
For a database, constraints are divided into column constraints (columns Constraint) and table Constraint.
A column constraint acts as part of the column definition only for the column itself. Table constraints, as part of a table definition, can be used for
multiple columns.
The following example illustrates the difference 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 constraint * *
