Five kinds of constraints in a database and how to add it __ database

Five major constraints
1.--PRIMARY KEY constraint (Primay key coustraint) uniqueness, Non-null
2.--Unique constraint (unique counstraint) uniqueness, can be empty, but can only have one
3.--Check Counstraint restrictions (such as age, gender, etc.) on the scope and format of the column data
Default constraint (default counstraint) for this data 4.--
5.--FOREIGN KEY constraints (Foreign key Counstraint) need to establish a relationship between the two tables and reference the columns of the primary table
Syntax examples of five major constraints
1.--Add PRIMARY KEY constraint (Stuno as primary key)
ALTER TABLE Stuinfo
Add constraint Pk_stuno primary key (Stuno)
2.--Add a UNIQUE constraint (ID number only, because everyone is different)
ALTER TABLE Stuinfo
Add constraint Uq_stuid unique (stuid)
3.--Add Default constraint (if address is not filled in by default, "address is unknown")
ALTER TABLE Stuinfo
Add constraint df_stuaddress default (' Address Unknown ') for stuaddress
4.--add Check constraint (age limit between 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= ' man ' or stusex= ' lady)

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

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

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).

1 PRIMARY keyword constraints
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]

2 Foreign keyword constraints
An external keyword constraint defines a 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:

REFERENCES
Specifies the information to establish the associated table.
Ref_table
Specifies the name of the table to which you want to establish an association.
Ref_column
Specifies the name of the related column in the table that you want to create the association.
On DELETE {CASCADE | NO ACTION}
Specifies the associated actions that are made to 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 | NO ACTION}
Specifies the associated actions that are made to an association table when 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.
Not for REPLICATION
The Foreign keyword constraint for the specified column does not take effect when inserting data copied from other tables 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: The temporary table cannot specify an external keyword constraint.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 * *

  Note:     1. Adding a PRIMARY KEY constraint automatically creates a unique index. If the focus index has not been created in the table, the focused unique index is created automatically. If the focus index already exists in the table, the nonclustered index is created automatically.     2. Adding a unique constraint automatically creates a unique index. If you do not add [nonclustered|clustered] after the unique keyword, the nonclustered index is created by default.