When designing a database, data integrity needs to be considered to ensure the quality of data in the database table (data integrity is the correctness and consistency of the index data ). For example, if you want to create a basic info table StudentInfo for a student, the name of the student in this table can be the same but the student ID must be different, and the student's age must be within a certain range, there are many "restrictions" like this. If these restrictions are violated, the distorted data that is inconsistent with reality will damage the integrity of the data. Because the database cannot determine which data is distorted by itself, it is necessary to consider adding some constraints to ensure data integrity.
There are five types of table constraints in the database:
1. Primary Key constraints (Primary Key constraint ):The data in the primary key column is unique and cannot be blank.
Syntax: Constraint Primary Key Constraint name Primary Key [Clustered | Nonclustered] (column name 1, [column name 2 ,......, Column name n])
A primary key is a column or a group of columns in a table. Its values can uniquely identify each row in the table.
2. Unique constraint (Unique constraint ):The data in this column is unique and can be null, but only one null value can appear (non-repetition ).
Syntax: Constraint name Unique [Clustered | Nonclustered] (column name 1, [column name 2 ,...... Column name n])
The Uniqueness constraint ensures that the data of one or more columns except the primary key is unique to prevent repeated values from being input in the column. (A table can only have one primary key constraint. If other columns do not want to have duplicates, you can use the uniqueness constraint .)
3. Check constraints ):Range and format restrictions
Syntax: Constraint name Check [Not For Replication] (logical expression)
Check constraints enable the data values or formats acceptable to one or more columns in the specified table.
4. Default constraints (Default constraint ):
Syntax: Constraint name Default Constraint expression [For column name]
Defines a default value for the specified column. When you enter data, if you do not enter the value of this column, set the value of this column to the default value.
5. Foreign Key constraints (Foreign Key constraint ):Inter-Table constraints ensure the integrity of data references
Syntax: Constraint name Foreign Key (column name 1, [column name 2 ,...... Column name n]) References joined table (joined column name 1, [joined column name 2 ,...... Join column name n])
You can create a connection between two tables by associating one or more columns in the current table to the primary key column of another table. The columns in the current table become Foreign keys. The foreign key involves two tables, one master table and one slave table. The foreign key in the master table is the primary key in the slave table.
The following uses the "Student Information" table as an example to add constraints.
Create table StudentInfo (StudentNo int not null, StudentID int, StudentName nvarchar (50), Sex varchar (2), Score float Default (0), -- set the Default value to 0 for the student's Score, when no data is input, the system automatically assigns 0. departmentNo varchar (10), Constraint PK_Student Primary Key (StudentNo), -- use the "student ID" as the Primary Key. If there is a student ID, you can uniquely identify a student Constraint IX_Student Unique (StudentID ), -- 'add uniqueness Constraint for the "ID Card". When the entered ID card number is repeated, the system will automatically Check the error Constraint CK_Student Check (Sex In ('male', 'female ')), -- 'check whether the gender of the student is male or female -- the 'Student Information 'table establishes foreign key constraints with the 'department' table, and the foreign key is the "department" of the student information table ", from the table's primary Key or candidate Key column to the "Department" table's "Record Number" Constraint FK_Student Foreign Key (DepartmentNo) References Department (RecordNo ))
There are two types of constraints to be created:
(1) create constraints when creating a table, that is, define constraints in CreateTable
Create StudentInfo (StudentNo vchar Primary Key ,......)
Create StudentInfo (StudentNo vchar ,...... Constraint PK_Student Primary Key (StudentNo )......)
(2) Add constraints to a created Table, that is, create constraints in Alter Table.
Alter Table StudentInfo add Constraint PK_Student Primary Key (StudentNo)
Delete constraint: For tables with foreign key constraints, if you delete a non-empty foreign key, an error may occur.
Alter Table StudentInfo Drop Constraint PK_Student
Constraints are a method provided by SQL Server to automatically maintain database integrity. They define conditions for data in a single column of an input table or table. For databases, constraints can also be divided into Column Constraint and Table Constraint. Column constraints act only on this Column as part of Column definitions, A table constraint can act on multiple columns as part of a table definition. Constraints are only a kind of table management in databases. These have been learned before, but are not very useful. Therefore, we should make a summary of the constraints.