Database Integrity and Constraints

reprint please indicate from Zhu Zhu Homeland http://blog.csdn.net/zhgl7688

Database Integrity and Constraints

1, data integrity: Common three types of constraints to ensure that data integrity has domain (column) integrity, entity integrity, referential integrity.

2. Entity integrity: Ability to uniquely identify the first record in a table.

The guaranteed method has a PRIMARY key constraint, an identity constraint, and a unique constraint.

Basic syntax for adding primary KEY constraints and UNIQUE constraints

Alter table table name addconstraint constraint name constraint type specific constraint description

Naming rules for constraint names: Constraint type _ constraint field.

such as the primary key (Primary key) constraint: Pk_stuno, the only (unique key) constraint Uq_stuidno.

Alter Table Students

Add constraint Pk_studentid Primary Key (studentid)

Alter Table Students

Add constraint Uq_studnetno Unique (studentno)

3. Domain integrity: The validity of specific column data in a table ensures that invalid values are not entered.

Guaranteed methods have check constraints, restricted data types, default values, and non-null constraints.

Name rules for constraint names: check (check key) constraint: ck_age; default key constraint: Df_address.

Alter Table Students

Add constraint Ck_age Check (agebetween and )

Alter Table Students

Add constraint df_address default (' address unknown ') for Address

4. Referential integrity: Maintain the validity and completeness of data between tables.

Constraint method: Establishes a foreign key, relates the primary key of another table, that is, the foreign KEY constraint

Name rule for constraint name: FOREIGN key (foreign key) constraint: Fk_loginid

Alter Table Students

Add constraint Fk_loginid Foreign Key (classid) References StudentClass (classid)

5, the use of foreign keys: required data type, data length must be identical to the corresponding primary key table field; To add data, you first add the primary key table, and then add the foreign key table; When you delete data, you first delete the foreign key table data, and then delete the primary key table data.

6. Complete database Creation steps:

Build Library-"build table-" PRIMARY KEY constraint-"Domain integrity constraint-" FOREIGN KEY constraint

reprint please indicate from Zhu Zhu Homeland http://blog.csdn.net/zhgl7688

Database Integrity and Constraints