Database five constraints

Five kinds of constraints in database and how to add them

Five major constraints

1.--PRIMARY KEY constraint (Primay key coustraint) uniqueness, non-nullability

2.--Unique constraint (unique counstraint) uniqueness, can be null, but only one

3.--Check Constraint (check counstraint) limits the scope and format of the column data (e.g. age, gender, etc.)

4.--default constraint (default counstraint) that data defaults

5.--FOREIGN KEY constraint (Foreign key counstraint) requires establishing a relationship between two tables and referencing the columns of the primary table

Syntax examples for the five major constraints

1.--Adding a PRIMARY KEY constraint (Stuno as the primary key)

ALTER TABLE Stuinfo

Add constraint Pk_stuno primary key (Stuno)

2.--Add a UNIQUE constraint (the ID number is unique because everyone is different)

ALTER TABLE Stuinfo

Add constraint Uq_stuid unique (stuid)

3.--Add a DEFAULT constraint (if the address is not filled by default is "address Unknown")

ALTER TABLE Stuinfo

Add constraint df_stuaddress default (' Address Unknown ') for stuaddress

4.--add Check constraint (age is limited to 15-40 years old)

ALTER TABLE Stuinfo

Add constraint ck_stuage Check (stuage between and 40)

ALTER TABLE Stuinfo

Add constraint ck_stusex check (stusex= ' male ' or stusex= ')

5.--adding foreign KEY constraints (primary table stuinfo and relationships from table Stumarks, associating fields Stuno)

ALTER TABLE Stuinfo

Add constraint Fk_stuno foreign key (Stuno) references Stuinfo (Stuno)

Constraint (Constraint) is a method that Microsoft SQL Server provides to automatically maintain database integrity, defining constraints that can be used to enter data in a single column of a table or table (for an Introduction to data integrity, see Chapter 9th). There are 5 constraints in SQL Server: PRIMARY KEY constraint (Primary key Constraint), foreign keyword constraint (Foreign key Constraint), uniqueness constraint (unique Constraint), check constraint CONSTRAINT) and DEFAULT constraints (default Constraint).

1 PRIMARY KEY constraint

The PRIMARY KEY constraint specifies that the value of a combination of one or more columns in a table is unique in the table, that is, a single row of records can be specified. Only one column per table is designated as the primary keyword, and columns of the image and text types cannot be specified as the primary keyword, nor does it allow the specified primary key column to have a null attribute.

The syntax for defining PRIMARY keyword constraints 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, the system automatically generates a constraint name.

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 key 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) is not NULL,

Price Money default 0.01,

Quantity smallint NULL,

Constraint pk_p

_ID PRIMARY Key

(P_ID, P_name)

) on [primary]

2 out-of-keyword constraints

The OUTER keyword constraint defines a relationship between tables. When the combination of one column or multiple columns in a table and the primary key definitions in other tables are the same, you can define the combination of these columns or columns as foreign keywords and set which columns in the table are associated. In this way, when the column values are updated in the table that defines the primary KEY constraint, the outer key column in the table with the OUTER keyword constraint associated with it in the other table is also updated accordingly. The effect of the outer keyword constraint is also that when inserting data into a table that contains an outer key, the system rejects inserting the data if the table associated with it does not have the same value as the inserted outer key column value. As with the primary key, you cannot create a foreign keyword with a column that is defined as a TEXT or image data type. The outer keyword consists of up to 16 columns.

The syntax for defining an outer 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:

REFERENCES

Specifies the information for the associated table to be established.

Ref_table

Specifies the name of the table to which the association is to be established.

Ref_column

Specifies the name of the related column in the table to which the association is to be established.

On DELETE {CASCADE | NO ACTION}

Specifies the actions that are made on the associated table when data is deleted from the table. In cases where a data row in a child table is associated with a corresponding data row in the parent table, if a value of cascade is specified, the corresponding data row in the child table is deleted when the parent table data 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 | NO ACTION}

Specifies the related actions that are made to the associated table when data in the table is updated. In cases where a data row in a child table is associated with a corresponding data row in the parent table, if a value of cascade is specified, the corresponding data row in the child table is updated when the parent table data 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.

Not for REPLICATION

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

Example 7-4: Create an order form that is associated with the product table created earlier

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: Temporary tables cannot specify a foreign keyword constraint.

Uniqueness Constraint

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 that the column can have a null property. Because the primary key value is unique, the primary key column no longer sets a uniqueness constraint. Only

Sexual constraints consist 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, where 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 Constraints

The check constraint sets the check condition on the values in the input column or the entire table to limit the input values and ensure the data integrity of the database. You can set compliance checks on each column.

The syntax for defining a CHECK constraint is as follows:

CONSTRAINT constraint_name

CHECK [Not for REPLICATION]

(Logical_Expression)

The parameters are described as follows:

Not for REPLICATION

Specifies that the check constraint does not occur when data copied from other tables is inserted into the table.

Logical_Expression

Specifies that the logical condition expression returns a value of TRUE or false.

Example 7-6: Create an order form where the ordered 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 any constraints other than check constraints on a computed column.

7.2.5 Default Constraints

The default constraint specifies the default value for a column by defining the default value for the column or by using the column of the database's default object binding table. SQL Server recommends using default constraints instead of specifying the default values for the columns by using the default values defined. See the "Data integrity" section for ways to bind default constraints.

The syntax for defining a DEFAULT constraint 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 a DEFAULT constraint when you create a table, you can only add a default constraint to a table that has already been created.

7.2.6 column constraints and table constraints

For a database, constraints are divided into column constraints (columns Constraint) and table constraints (table Constraint).

A column constraint acts only on the column itself as part of the column definition. Table constraints, as part of the table definition, can be used to

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

Database five constraints