MySQL study NOTE 4: integrity constraints

Integrity constraints are imposed on fields to meet the expected results of this field.

For example, a field contains a default value and cannot be null.

Directly speaking, if the inserted data does not meet the restrictions, the database management system rejects the operation.

 

Set the table's primary key

The primary key identifies the uniqueness of each piece of information in the table, just as the relationship between the ID card number and the person

A person can have the same name, but the ID card number is unique,

The primary key is created to quickly find a piece of information in the table.

 

Single-field primary key

MySQL> Create TableStudent (->IDInt Primary Key,->NameVarchar(20),->Sex Boolean->); Query OK,0Rows affected (0.09Sec)
 

Three fields are created, with ID as the primary key.

 

Multi-field primary key

A multi-field primary key is composed of multiple attributes. The primary key is set 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. The combination of ID and course_id can determine a unique record.

 

Set the foreign key of the table

The table's foreign key corresponds to the primary key. For example, the ID in Table A is a foreign key, and the ID in Table B is a primary key.

Therefore, table B can be called the parent table and Table A as the child table.

The role of setting a foreign key is to establish a connection with the parent table. For example, after a student with the ID of 123 in Table B deletes the table, the record with the ID of 123 in Table A disappears.

This is designed to ensure table integrity.

MySQL >   Create  Table  Student3 (  -> ID Int   Primary   Key  ,  -> Course_id Int  ,  -> Teacher Varchar ( 20  ),  ->  Constraint FK Foreign   Key  (ID, course_id)  ->   References  Student2 (ID, course_id)  ->  ); Query OK,  0 Rows affected ( 0.12 Sec)

Create the student3 table. The FK following the constraint is the foreign key alias, and the foreign key is the field for setting the foreign key.

The content after references indicates the parent table and the primary key of the parent table.

Note that the primary key of the parent table cannot be empty and the Data Type of the primary key and the foreign key must be consistent.

 

Set non-empty table Constraints

It is easy to understand that the field value in the table cannot be null)

If a null value is inserted in a field with this constraint condition already set, the database system reports an error.

MySQL> Create TableStudent4 (->IDInt Not Null,->NameVarchar(20),->Sex Boolean->); Query OK,0Rows affected (0.10Sec)
 

Not null is the constraint.

 

Set table uniqueness constraints

Uniqueness means that the value of this field in the table cannot be repeated and the uniqueness constraint of the table is set.

That is, add unique to a field in the table.

 
MySQL> Create TableStudent5 (->IDInt Unique,->NameVarchar(20)->); Query OK,0Rows affected (0.10Sec)
 

The ID field must be unique.

 

Set the attribute value of the table to automatically increase

Auto_increment is mainly used to automatically generate a unique ID for the new records inserted in the table.

Only one field in a table can use the auto_increment constraint.

And this field must be part of the primary key.

 
MySQL> Create TableStudent6 (->IDInt Primary KeyAuto_increment,->NameVarchar(20)->); Query OK,0Rows affected (0.12Sec)
 

The ID here is the primary key, and the id value will be automatically added, such as 1, 2, 3, 4 ......

Note that the value of the auto_increment constraint must be of the integer type.

 

Set the default values of the attributes in the table.

When a new record is inserted in the table, if this field is not assigned a value

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

MySQL> Create TableStudent7 (->IDInt Primary Key,->ScoreInt Default 0->); Query OK,0Rows affected (0.10Sec)
 

The score field is 0 by default.