Detailed explanation of Mysql constraints

The blog talks about an important knowledge point in database - constraints

1, What are constraints

constraint

Constraints are actually constraints on the data in the table

2, Restraint effect

The purpose of adding constraints in table design is to ensure that the records in the table are complete and effective

For example, in the name field, you need to add constraints to make the user name not duplicate. Or you need to add an email when you have to register

3, Constraint type

Non NULL constraint (not null)
Uniqueness constraint
Primary key constraint
Foreign key constraint fk
Check constraints (currently not supported by MySQL and Oracle)
The above constraints are described one by one below

4, Non NULL constraint

The field constrained with not null cannot be null value, and specific data must be given

Create a table and add non empty constraints to the fields (create a user table, and the user name cannot be empty)

mysql> create table t_user(
-> id int(10),
-> name varchar(32) not null
-> );
Query OK, 0 rows affected (0.08 sec)

If the name field data is not inserted, an error will be reported

mysql> insert into t_user (id) values(1);
ERROR 1364 (HY000): Field 'name' doesn't have a default value

5, Uniqueness constraint

The field of unique constraint is unique, non repeatable, but can be null

Create a table to ensure that the mailbox address is unique (column level constraint)

mysql> create table t_user(
-> id int(10),
-> name varchar(32) not null,
-> email varchar(128) unique
-> );
Query OK, 0 rows affected (0.03 sec)

1. Table level constraint

mysql> create table t_user(
-> id int(10),
-> name varchar(32) not null,
-> email varchar(128)，
-> unique(email)
-> );

If you insert the same email, an error will be reported

mysql> insert into t_user(id,name,email) values(1,'xlj','932834897@qq.com');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_user(id,name,email) values(2,'jay','932834897@qq.com');
ERROR 1062 (23000): Duplicate entry '932834897@qq.com' for key 'email'

2. Use table level constraints to combine constraints for multiple fields
Joint constraint, which means that two or more fields are equal to another record at the same time, an error is reported

mysql> create table t_user(
-> id int(10),
-> name varchar(32) not null,
-> email varchar(128),
-> unique(name,email)
-> );
Query OK, 0 rows affected (0.01 sec)

Insert first data

mysql> insert into t_user(id,name,email) values(1,'xxx','qq.com');
Query OK, 1 row affected (0.05 sec)

It is also possible to insert the second piece of data if it is the same as one in the union field and the other

mysql> insert into t_user(id,name,email) values(2,'mmm','qq.com');
Query OK, 1 row affected (0.05 sec)

Insert the third piece of data. If it is the same as the union field, an error will be reported

mysql> insert into t_user(id,name,email) values(3,'mmm','qq.com');
ERROR 1062 (23000): Duplicate entry 'mmm-qq.com' for key 'name'

3. Table level constraints can be named (which can be used to delete the constraint in the future)

mysql> create table t_user(
-> id int(10),
-> name varchar(32) not null,
-> email varchar(128),
-> constraint t_user_email_unique unique(email)
-> );
Query OK, 0 rows affected (0.06 sec)

Constraint is the constraint keyword, t_user_email_unique name

Example: the user name cannot be empty or duplicate

name varchar(32) not null unique

6, Primary key constraint

Table design must have a primary key

1. Terms involved in primary keys
Primary key constraint
Primary key field
Primary key value
2. The relationship between the above three terms
After adding a primary key constraint to a field in the table, the field is called a primary key field, and every data appearing in the primary key field is called a primary key value

3. The primary key constraint is different from "not null unique"
After adding a primary key constraint to a field, the field cannot be repeated or empty. The effect is the same as that of the "not null unique" constraint, but it is different in essence.

In addition to "not null unique", the primary key constraint also adds "index - index" by default

4. A table should have a primary key field. If not, it means that the table is invalid
Primary key value: the unique identifier of the current row data and the ID number of the current row data.
Even if the related data of the two rows of records in the table are the same, they are considered to be two rows of records with different primary key values
5. Classification by number of fields constrained by primary key
Whether it is a single primary key or a composite primary key, a table can only have one primary key constraint (there can only be one constraint, but it can affect several fields)

Single primary key: add a primary key constraint to a field
Compound primary key: add a primary key constraint to the union of multiple fields (it can only be defined at the table level)
Single primary key (column level definition)

mysql> create table t_user(
-> id int(10) primary key,
-> name varchar(32)
-> );
Query OK, 0 rows affected (0.07 sec)

Single primary key (table level definition)

mysql> create table t_user(
-> id int(10),
-> name varchar(32) not null,
-> constraint t_user_id_pk primary key(id)
-> );
Query OK, 0 rows affected (0.01 sec)

Composite primary key (table level definition)

mysql> create table t_user(
-> id int(10),
-> name varchar(32) not null,
-> email varchar(128) unique,
-> primary key(id,name)
-> );
Query OK, 0 rows affected (0.05 sec)

6. A self increasing number is provided in MySQL database, which is specially used to automatically generate the primary key value. The primary key value is generated automatically without user maintenance. The self increasing number starts from 1 and increases with 1 (auto_increment)

mysql> create table t_user(
-> id int(10) primary key auto_increment,
-> name varchar(32) not null
-> );
Query OK, 0 rows affected (0.03 sec)

When two rows of records are inserted, the id primary key value will increase automatically

mysql> insert into t_user(name) values('jay');
Query OK, 1 row affected (0.04 sec)

mysql> insert into t_user(name) values('man');
Query OK, 1 row affected (0.00 sec)


mysql> select * from t_user;
+----+------+
| id | name |
+----+------+
| 1 | jay |
| 2 | man |
+----+------+
2 rows in set (0.00 sec)

7, Foreign key constraint fk

Can only be table level definitions (as in the following example)

foreign key(classno) references t_class(cno)

What is a foreign key
If there are two tables A and B, id is the primary key of A, and there is also an id field in B, then id is the foreign key of table B. the foreign key constraint is mainly used to maintain the data consistency between the two tables.

A is the basic table and B is the information table

1. Terms involved in foreign keys
Foreign key constraint
Foreign key field
Foreign key value
2. Relationships among foreign key constraints, foreign key fields, and foreign key values
After a foreign key constraint is added to a field, the field is called a foreign key field, and each data in the foreign key field is a foreign key value

3. Classification by field number of foreign key constraints
Add a foreign key constraint to a single field
Compound foreign key: add a foreign key constraint to the union of multiple fields
4. A table can have multiple foreign key fields (different from the primary key)
5. Analysis scenario
Design database tables to store student and class information

Two schemes
Scheme 1: store student information and class information in a table

sno sname classno cname
1 jay 100 Class 1, senior 3, Zhejiang No. 1 middle school
2 lucy 100 Class 1, senior 3, Zhejiang No. 1 middle school
3 king 200 Class 2, senior 3, Zhejiang No. 1 middle school

Disadvantages: redundant data, such as too many duplicate data in cname field

Scheme 2: separate student information and class information into two tables
Student table (add single foreign key)

sno(pk) sname classno(fk)
1 jack 100
2 lucy 100
3 king 200

Class schedule

cno(pk) cname
100 Class 1, senior 3, Zhejiang No. 1 middle school
200 Class 2, senior 3, Zhejiang No. 1 middle school

conclusion

In order to ensure that the data in the classno field in the student table must come from the data in the cno field in the class table, it is necessary to add foreign key constraints to the classno field in the student table

Be careful
Foreign key value can be null
When a foreign key field references a field of a table, the referenced field must have a unique constraint
With foreign key references, tables are divided into parent and child tables
Class table: parent table
Student table: sub table
Create parent table before creating
Delete sub table data first
Insert parent table data first
Store student class information

mysql> drop table if exists t_student;
mysql> drop table if exists t_class;

mysql> create table t_class(
-> cno int(10) primary key,
-> cname varchar(128) not null unique
-> );

mysql> create table t_student(
-> sno int(10) primary key auto_increment,
-> sname varchar(32) not null,
-> classno int(3),
-> foreign key(classno) references t_class(cno)
-> );

mysql> insert into t_class(cno,cname) values(100,'aaaaaaxxxxxx');
mysql> insert into t_class(cno,cname) values(200,'oooooopppppp');
mysql> insert into t_student(sname,classno) values('jack',100);
mysql> insert into t_student(sname,classno) values('lucy',100);
mysql> insert into t_student(sname,classno) values('king',200);

Class table t_class

mysql> select * from t_class;
+-----+--------------+
| cno | cname |
+-----+--------------+
| 100 | aaaaaaxxxxxx |
| 200 | oooooopppppp |
+-----+--------------+

Student form t_student

mysql> select * from t_student;
+-----+-------+---------+
| sno | sname | classno |
+-----+-------+---------+
| 1 | jack | 100 |
| 2 | lucy | 100 |
| 3 | king | 200 |
+-----+-------+---------+

Find the class name of each student in the table above

mysql> select s.*,c.* from t_student s join t_class c on s.classno=c.cno;
+-----+-------+---------+-----+--------------+
| sno | sname | classno | cno | cname |
+-----+-------+---------+-----+--------------+
| 1 | jack | 100 | 100 | aaaaaaxxxxxx |
| 2 | lucy | 100 | 100 | aaaaaaxxxxxx |
| 3 | king | 200 | 200 | oooooopppppp |
+-----+-------+---------+-----+--------------+

conclusion
The above is a typical one to many design: add foreign keys in multiple places (sub table plus foreign keys)