MySQL ensures data integrity

Data Integrity Overview

1. entity integrity

An object is an object in the world. Every row in RDBMS represents an object. Entity integrity means that every entity can be differentiated. Therefore, entity integrity is generally achieved through primary key constraints, unique constraints, or column attributes.

2. Domain integrity

It mainly means that the input of a column must be valid. It can be achieved by limiting the data type, format, or value range of the column.

3. Integrity of reference

The relationship between a table and a table can be achieved through a foreign key.

4. User-Defined integrity

I am not very familiar with it. It can be implemented through triggers and stored procedures. I will be more familiar with Oracle later.

Data Integrity implementation

1. create non-empty constraints (domain integrity)

The not null keyword. The specified Column cannot enter a null value.

Example:

Name varchar (50) not null;

2. Set primary key constraints (entity integrity)

The primary key keyword ensures that the data in the specified Column cannot be repeated and cannot be blank. A table can only define one primary key.

Example:

Id int (10) primary key not null;

3. Set unique constraints (domain integrity, because it is a restriction on columns)

Unique keyword. Sometimes a table requires multiple columns to be unique, and a table can only be composed of one primary key. Therefore, you can set unique constraints. A table can have multiple unique constraints.

Example:

PhoneNo varchar (20) unique;

4. Specify the default value (domain integrity)

The default keyword. If no column value is input when a piece of data is generated, you can specify a default value.

Example:

PhoneNo varchar (20) default '20140901 ';

5. Set check constraints (domain integrity)

The check keyword takes effect only when a new row is inserted or when an existing row is changed. It is used to prevent a value that does not meet the conditions from entering the column. It is invalid for the null value, because inserting null is equivalent to not inserting. One column can have multiple check

Example:

Age int check (age between 10 and 20 );

Because mysql does not support it, it is not very clear now. I will fill this gap in the future.

6. Use the auto-numbered Column

Auto_increment keyword. When you add a record, the value is automatically added to the row, and the value increases automatically. Generally used together with the primary key

Example:

Id int primary key auto_increment not null;

7. Create a foreign key constraint

Syntax:

Fk_name varchar (50) references reference table name (column name)

Note: The column name of the referenced table must be the primary key, and the reference relationship must be deleted or the current table must be deleted when the referenced table is deleted.

For example, one table represents the class, and the other table represents the student. When deleting the class, you must delete the reference relationship or delete all the students.