Database integrity and constraints, database integrity constraints

Database integrity and constraints, database integrity constraints

Reprint please indicate from Zhu jiayuan http://blog.csdn.net/zhgl7688

Database integrity and constraints

1. Data Integrity: three types of constraints are commonly used to ensure data integrity: domain (column) Integrity, entity integrity, and reference integrity.

2. entity Integrity: uniquely identifies the first record in the table.

The primary key constraint, identity constraint, and unique constraint are supported.

Basic syntax for adding primary key constraints and unique constraints

Alter table name addconstraint constraint name Constraints

Naming rule for constraint names: constraint type _ constraint field.

For example, the Primary Key constraint is PK_StuNo, and the Unique (Unique Key) constraint is UQ_StuIdNo.

Alter tablestudents

Add constraintPK_StudentId primarykey (StudentId)

Alter tableStudents

Add constraintUQ_StudnetNo unique (studentNo)

3. Domain integrity: the data in a specific column in the table is valid, so that no invalid value is entered.

Ensure that the methods have check constraints, data types, default values, and non-empty constraints.

Naming rule for constraint names: check key constraint: CK_Age; default key constraint: DF_Address.

Alter tablestudents

Add constraintCK_Age check (agebetween 15 and 28)

Alter tablestudents

Add constraintDF_Address default ('address unknown ') for Address

4. Integrity of reference: Maintain the validity and integrity of data between tables.

Constraint Method: Create a foreign key and associate it with the primary key of another table, that is, the foreign key constraint.

Naming rule for constraint names: foreign key constraint: FK_LoginId

Alter tablestudents

Add constraintFk_LoginId foreignkey (classid) references studentclass (classid)

5. Foreign Key Usage: the data type and Data Length must be exactly the same as the corresponding primary key table field. When adding data, you must first add a primary key table and then add a foreign key table; when deleting data, you must first Delete the data in the foreign key table and then the data in the primary key table.

6. Complete database creation steps:

Database creation-Table creation-primary key constraints-domain integrity constraints-foreign key constraints

Reprint please indicate from Zhu jiayuan http://blog.csdn.net/zhgl7688