preface

When creating a table, you can add corresponding constraints to the fields of the table. The purpose of adding constraints is to ensure the legitimacy, validity and integrity of the data in the table.

Common constraints

name	explain
Non NULL constraint (not null)	The field of the constraint cannot be NULL
Unique constraint	Constrained fields cannot be duplicate
Primary key constraint	Constrained fields cannot be NULL or duplicate (PK for short)
foreign key constraint	It is used to restrict the relationship between two tables and ensure that the value of this field must come from the value of the associated column of the main table (FK for short)
Check constraints	Note that Oracle database has a check constraint, but mysql does not. At present, mysql does not support this constraint

Non NULL constraint (not null)

The field decorated with not null cannot be empty when inserting data into the table.

create table t_user (
	no bigint,
    name varchar(255) not null
);

Execute the following statement( name Field is empty):
insert into t_user(no) values(1);

report errors:
[SQL]insert into t_user(no) values(1);
[Err] 1364 - Field 'name' doesn't have a default value

You can also add a default value to the not null field, so that the above error will not be reported:

create table t_user (
	no bigint,
    name varchar(255) default 'xxx' not null
);

Note: the not null constraint has only column level constraints. There are no table level constraints.

unique constraint

unique constraints cannot have duplicate fields.

1. The setting number is unique (column level constraint: this constraint applies only to the relevant column):

create table t_user (
	no bigint unique,
    name varchar(255)
);

Insert the same number:

insert into t_user(no,name) values (1,'Xiao Ming');
insert into t_user(no,name) values (1,'Bruce Lee');

report errors:
Error Code: 1062. Duplicate entry '1' for key 't_user.no'

2. Add unique to two or more columns (table level constraint: can be applied to multiple columns in a table)

Create a table and set unique(no,name)

create table t_user(
	no bigint,
    name varchar(255),
    password varchar(15),
    unique(no,name)
);

Execute the following code:
insert into t_user(no,name) values (1,'Xiao Ming');
insert into t_user(no,name) values (1,'Bruce Lee');
select * from t_user;

There is no error in the operation. The statement results of the above table are as follows: unique(no,name)Only in no and name An error will be reported only when the is the same:

However, if no and name are set to unique respectively as follows, it is not a table level constraint, but two column level constraints, and an error will be reported:

drop table if exists t_user;
create table t_user(
	no bigint unique,
    name varchar(255) unique,
    password varchar(15)
);
insert into t_user(no,name) values (1,'Xiao Ming');
insert into t_user(no,name) values (1,'Bruce Lee');

primary key constraint

Primary key constraint:
The primary key constraint is added. The data in the primary key field cannot be NULL or duplicate.

Primary key constraint, primary key field and primary key value:
After adding a primary key constraint to a field in the table, the field is called a primary key field, and every data in the primary key field is called a primary key value;

(1) The primary key field cannot be duplicate or empty. The effect is not the same as "not null nuique", but the essence is different. After adding a primary key constraint, the primary key will not only have the effect of "not null unique", but also automatically add "index index" to the primary key field

(2) a table should have a primary key. If not, it means that the table is invalid. (the first paradigm requires that any table should have a primary key). The primary key value is the unique identification of the current row data. The primary key value is the ID number of the current row data; (even if the two rows of data in the table are exactly the same, but because the primary key is different, it is considered that the data of the two rows are completely different).

A table can only have 1 primary key constraint.

1. Single primary key

Single primary key: a field in a table is used as the primary key.

Create a table and set the number as the primary key:

create table t_user(
		no bigint primary key,
        name varchar(255) unique
);

Run the following two sections respectively sql，Will report an error:

insert into t_user(no,name) values (1,'Xiao Ming');
insert into t_user(no,name) values (1,'Bruce Lee');

insert into t_user(name) values ('Bruce Lee');
Test results: no It is a primary key. Because a primary key constraint is added, the data in the primary key field cannot be null NULL，Nor can it be repeated.

Define primary keys using table level constraints:
create table t_user(
		no bigint,
        name varchar(255) unique,
        primary key(no)
);

2. Composite primary key

drop table if exists t_user;
create table t_user(
		no bigint,
        name varchar(255),
        primary key(no,name)
);

Not all values in the composite primary key are duplicate, so they are not duplicate
 This is allowed:

insert into t_user(no,name) values (1,'Xiao Ming');
insert into t_user(no,name) values (1,'Bruce Lee');
select * from t_user;

3. Natural primary key and business primary key

Natural primary key:
The primary key value should preferably be a natural number that has nothing to do with business. (this method is recommended)

Business PK:
The primary key value is linked to the system's business. For example, the key card is the card number of the bank card, and the ID number is the primary key.

It's best not to use the field linked to the business as the primary key. Once the business changes in the future, the primary key value may need to change, but sometimes there is no way to change, because the change may lead to duplicate primary key values.

4. MySQL provides auto_increment for the primary key value

When creating a table, the no field starts with 1 and increases by 1:

drop table if exists t_user;
create table t_user(
	no bigint primary key auto_increment,
    name varchar(255)
);
insert into t_user(name) values ('a');
insert into t_user(name) values ('b');
insert into t_user(name) values ('c');
insert into t_user(name) values ('d');
insert into t_user(name) values ('e');
select * from t_user;

foreign key constraint

The foreign key is used to establish the association between the master table and the slave table, establish a connection for the data of the two tables, and restrict the consistency and integrity of the data in the two tables.

Foreign key constraint, foreign key field, foreign key value: after adding a foreign key constraint to a field, the field is called a foreign key field, and the data in the foreign key field is called a foreign key value.

When defining a foreign key, you need to follow the following rules:

The parent table must already exist in the database or be the table currently being created. In the latter case, the parent table and the child table are the same table. Such a table is called a self referencing table, and this structure is called self referencing integrity.

A primary key must be defined for the parent table.

The primary key cannot contain null values, but null values are allowed in foreign keys. That is, as long as each non null value of a foreign key appears in the specified primary key, the content of the foreign key is correct.

Specify a column name or combination of column names after the table name of the parent table. This column or combination of columns must be the primary key or candidate key of the parent table.

The number of columns in the foreign key must be the same as the number of columns in the primary key of the parent table.

The data type of the column in the foreign key must be the same as that of the corresponding column in the primary key of the parent table.

Foreign keys can be NULL.

Create a table. An area_number of 1 represents the Chinese region and 0 represents the overseas region:

drop table if exists t_user;
create table t_user(
		no bigint primary key auto_increment,
        name varchar(255),
        area_number int(1),
        area char(4)
);
insert into t_user(name,area,area_number) values('Xiao Ming','China region',1);
insert into t_user(name,area,area_number) values('Bruce Lee','China region',1);
insert into t_user(name,area,area_number) values('Ana','Overseas area',0);
insert into t_user(name,area,area_number) values('Jan','Overseas area',0);
insert into t_user(name,area,area_number) values('Fak','Overseas area',0);
insert into t_user(name,area,area_number) values('Small method','China region',1);
select * from t_user;

You can clearly see the disadvantage of this table: the data is too redundant!!

Solution: we can use two tables to maintain this user information. Use the user table t_user and the region table t_area to store data respectively.

drop table if exists t_user;
drop table if exists t_area;

create table t_area(
		area_number int(1) primary key,
        area char(4)
);
create table t_user(
		no bigint primary key auto_increment,
        name varchar(255),
        a_number int(1),
        foreign key(a_number) references t_area(area_number)
);

insert into t_area values (1,'China region'),(0,'Overseas area');
insert into t_user(name,a_number) values ('Xiao Ming',1), ('Bruce Lee',1),('Ana',0),('Jan',0),('Fak',0),('Small method',1);

select * from t_area;
select * from t_user;

Through two table operation, the data is no longer redundant.

Find out the user's information:

select u.*,a.* from t_user u join t_area a on u.a_number=a.area_number;

At this time, the t_area table is called the parent table and the t_user is called the child table.

Operation order of parent and child tables

When deleting data, delete the child table first and then the parent table.
When adding data, first add the parent table, and then add the child table.
When creating a table, first create a parent table, and then create a child table.
When deleting a table, delete the child table first and then the parent table.