Database integrity and constraints, database integrity constraints

Source: Internet
Author: User

Database integrity and constraints, database integrity constraints

Reprint please indicate from Zhu jiayuan

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.