MySQL study note 4: integrity constraints limit fields

The integrity constraint is to restrict the field so that it fits the desired effect such as the field having a default value, not NULL, etc
The direct point of view says: if the inserted data does not meet the restriction requirements, the database management system will refuse to perform the operation
Sets the primary key of the table
The primary key identifies the uniqueness of each piece of information in the table, as does the relationship between the id number and the person
People can have the same name, but the id 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, id being the primary key
Multiple field primary keys
The primary key of multiple fields is composed of multiple properties. The primary key is set in series 1 after the properties are 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 record with only one
Sets the foreign keys of the table
The foreign key of the table corresponds to the primary key. For example, id in table A is the foreign key, and id in table B is the primary key
Then you can call the table B the parent table and the table A the child table
The function of setting the foreign key of the table is to establish the connection with the parent table. For example, after the deletion of the student whose id is 123 in the table B, the record whose id is 123 in the table A also disappears with the deletion of the student whose id is 123 in the table B
The purpose of this is to ensure the integrity of the table

 
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) 

Here you create the student3 table, fk after 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
It is important to note that the primary key in the parent table cannot be empty and that the primary key and foreign key have data types 1 to 1
Sets the non-empty constraint for the table
Non-nullability is easy to understand, which means setting the value of a field in a table not to be empty (NULL)
If you insert a null value in a field where this constraint condition has been set, the database system will report an error

 
mysql> create table student4( 
-> id int not null, 
-> name varchar(20), 
-> sex boolean 
-> ); 
Query OK, 0 rows affected (0.10 sec) 

So not, null is the constraint
Sets the exclusive constraint for the table
Singleness means that the value of the field in the table cannot be repeated, and sets the singleness constraint of the table
That is, adding 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) 

Here the id field is not repeatable
Set the table property values to automatically increase
auto_increment is primarily used to automatically generate a 1-only ID for new records inserted into the table
Only one field in a table can use the auto_increment constraint
And the field must be part 1 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) 

Here id is the primary key and automatically increments id, such as 1,2,3,4...
It is important to note that the value of the auto_increment constraint must be of type integer
Sets the default values for the properties in the table
When inserting a new record into a table, if no value is assigned to that field
The database system will automatically assign 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)