Five kinds of constraints in database and how to add them

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 empty, but there can only be one
3.--CHECK constraints (check counstraint)
Limits on the range and format of data in this column (e.g. age, gender, etc.)
4.--default constraint (default counstraint) that data defaults
5.--FOREIGN KEY constraint
(Foreign Key Counstraint)
You need to establish a relationship between two tables and reference 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= ' man ' or
stusex= ' Women
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 Microsoft SQL Server
Provides a way to automatically maintain database integrity, defining constraints that can be used to enter data in a single column of a table or table (see Chapter 9th for an Introduction to data integrity). In SQL Server, there are 5
Constraints: PRIMARY KEY constraint (Primary key Constraint), outer keyword constraint (Foreign key Constraint), uniqueness constraint (unique
Constraint), check constraints (check Constraint), and Default constraints (default Constraint).
1
Primary keyword 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 the image
and text type columns 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
Specify an index category, CLUSTERED
to 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
Outside 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 use a defined
The TEXT or image data type of the column is created outside the keyword. 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 row of data in the child table is deleted when the parent table data row is deleted; If no is specified
Action, 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; If no is specified
Action, 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. The uniqueness constraint specifies that the column can have null
Property. Because the primary key value is unique, the primary key column no longer sets a uniqueness constraint. The uniqueness constraint is up to 16
A column.
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 a 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
The default constraint is recommended instead of specifying the default value of the column by using the default value 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
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 */

Five kinds of constraints in database and how to add them