Integrity constraints in MySQL

For a table that has already been created, although the data type of the field determines the type of data that can be stored, the data stored in the table is not checked for legality.

MySQL-Supported integrity constraints:

• The value of the NOT NULL constraint field cannot be null
• Default SET field defaults
• The value of the unique KEY (UK) constraint field is unique
• The PRIMARY key (PK) constraint field is the primary key for the table and can be used as a unique identifier for the table record
• The value of the auto_increment constraint field is automatically incremented
• FOREIGN key (FK) constraint field is a foreign key of the table

Set a non-null constraint on a table

Non-nullability is a good understanding, that is, setting the value of a field in a table cannot be empty (null)

If you insert a null value in a field that already has this constraint condition set, the database system will error.

MySQL>createtable  student4 (    int not NULL ,      - varchar (),      - Sex Boolean      );

You can see a description of the table with DESC table or show full columns from table. For example, the table above:

Mysql> descstudent4;+-------+-------------+------+-----+---------+-------+|Field|Type| Null | Key | Default |Extra|+-------+-------------+------+-----+---------+-------+|Id| int( One)|NO|     | NULL    |       ||Name| varchar( -)|YES|     | NULL    |       ||Sex| tinyint(1)|YES|     | NULL    |       |+-------+-------------+------+-----+---------+-------+3Rowsinch Set(0.00Sec

See null that part is NULL.

Set uniqueness constraints on a table

Uniqueness refers to the value of the field in the table that cannot be repeated, setting a Uniqueness constraint on the table

That is, adding unique to a field in a table

Mysql> CREATE TABLE STUDENT5 (    ID int unique,    name varchar)    ; Query OK, 0 rows affected (0.10 sec)

The ID field is not repeatable here.

If you want to set a name on the UK for the field ID, you can execute the SQL statement constraint, when you create the table, the specific SQL statement is as follows:

MySQL>createtable  student5 (    intUnique  ,    varchar(+),        CONSTRAINTUNIQUE(ID)    );

Setting the primary key for a table

The primary key can identify the uniqueness of each piece of information in the table, like the identity card number and the person's relationship

People can have the same name, but the identity card number is unique,

The purpose of creating a primary key is to quickly find a piece of information in a table

Single Field primary key

Mysql> CREATE TABLE student (    ID int primary KEY,    name varchar),    sex Boolean    ); Query OK, 0 rows affected (0.09 sec)

Three fields created with ID as primary key

Multi-field Primary key

A multi-field primary key is composed of multiple attributes, and the primary key is set uniformly after the attribute is defined

Mysql> CREATE TABLE Student2 (    ID int,    course_id int,    score float,    primary KEY ( id,course_id)    ; Query OK, 0 rows affected (0.11 sec)

The Student2 table has three fields, where the combination of ID and course_id can determine a unique record

Set foreign keys for a table

The foreign key of the table corresponds to the primary key, such as the ID in table A is a foreign key, and the ID in table B is the primary key

Then you can call table B A parent table, table A is a child table

Setting a table foreign key is to establish a connection to the parent table, such as when a student with ID 123 in table B is deleted, the record with ID 123 in table a disappears as well

This is done to ensure that the integrity of the table

Mysql> CREATE TABLE STUDENT3 (    ID int primary KEY,    course_id int,    teacher varchar), 
   -> constraint FK foreign key (id,course_id),    references Student2 (id,course_id)    ; Query OK, 0 rows affected (0.12 sec)

The Student3 table is created here, and the FK behind constraint is the foreign key alias, foreign key is the field that sets the foreign key

The content after references represents the parent table, and the primary key in the parent table

Note that the primary key in the parent table cannot be empty, and the data types of the primary and foreign keys are consistent

Setting the property value of a table automatically increases

Auto_increment is primarily used to automatically generate a unique ID for new records inserted in a table

A table can have only one field using the auto_increment constraint

And the field must be part of the primary key

Mysql> CREATE TABLE STUDENT6 (    ID int primary key auto_increment,    name varchar)    ; Query OK, 0 rows affected (0.12 sec)

The ID here is the primary key, and the ID value is automatically incremented, such as 1,2,3,4 ...

It is important to note that the value of the auto_increment constraint must be an integer type

Set default values for properties in a table

When you insert a new record in a table, if the field is not assigned a value

The database system automatically assigns a default value to the field.

Mysql> CREATE TABLE student7 (    ID int primary KEY,    score int default 0    ); Query OK, 0 rows affected (0.10 sec)

The score field here will default to 0

Integrity constraints in MySQL