MySQL study Note 4: integrity constraints field _ MySQL

MySQL study Note 4: integrity constraints restricted field bitsCN.com integrity constraints are to limit the field, so as to meet the field to achieve our expected results, such as the field contains the default value, cannot be NULL, etc.
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 table student (
-> Id int primary key,
-> Name varchar (20 ),
-> Sex boolean
-> );
Query OK, 0 rows affected (0.09 sec)

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 table student4 (
-> Id int not null,
-> Name varchar (20 ),
-> Sex boolean
-> );
Query OK, 0 rows affected (0.10 sec)

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 table student5 (
-> Id int unique,
-> Name varchar (20)
-> );
Query OK, 0 rows affected (0.10 sec)

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 table student6 (
-> Id int primary key auto_increment,
-> Name varchar (20)
-> );
Query OK, 0 rows affected (0.12 sec)

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 table student7 (
-> Id int primary key,
-> Score int default 0
-> );
Query OK, 0 rows affected (0.10 sec)
BitsCN.com