MySQL Ⅲ database constraints

MySQL

Database constraints

null constraint

drop table if exists student;
create table student( 
	id int, 
	sn int, 
	name varchar(20) not null,
	qq_mail varchar(20) 
);

Indicates that the name field cannot be null

insert into student(id,sn,name,qq_mail) values(1,101,NULL,'[email protected]');

When we set it to null, an error will be reported

Insert picture description here

unique constraint

drop table if exists student;
create table student( 
	id int, 
	sn int unique, 
	name varchar(20) not null,
	qq_mail varchar(20) 
);
insert into student(id,sn,name,qq_mail) values(1,101,'A','[email protected]');
insert into student(id,sn,name,qq_mail) values(2,101,'B','[email protected]');
Insert picture description here

default default value constraint

drop table if exists student;
create table student( 
	id int, 
	sn int unique, 
	name varchar(20) not null,
	qq_mail varchar(20) default '[email protected]'
);

If no value is given, the default value will be displayed

insert into student(id,sn,name,qq_mail) values(1,101,'A','[email protected]');
insert into student(id,sn,name,qq_mail) values(2,102,'B',null);
insert into student(id,sn,name) values(3,103,'C');
Insert picture description here

primary key primary key constraint

Is a combination of not null and unique

When a field is modified by the primary key, then this field cannot be empty and is unique.
General match: auto_increment (self-increment)

drop table if exists student;
create table student( 
	id int primary key auto_increment, 
	sn int unique, 
	name varchar(20) not null,
	qq_mail varchar(20) default '[email protected]'
);

Note: After the table is created, there is no data in the table. When the insert is executed for the first time, the current primary key (id) will automatically increase from 1

When the first data is inserted, the id is 1, but when the data is deleted, the table is empty, and then another data is inserted. The id of 2
means that on the basis of the previous insertion, id+1
can only remove the entire table. After deleting, when inserting again, it starts from 1

insert into student(sn,name,qq_mail) values(101,'A','[email protected]');
insert into student(sn,name,qq_mail) values(102,'B','[email protected]');
Insert picture description here


A table is a primary key, this primary key can make two fields together to represent

foreign key foreign key constraint

Foreign key is used to associate the primary key or unique key of other tables
foreign key (字段名) references 主表(列)

drop table if exists classes; 
create table classes( 
	id int PRIMARY KEY AUTO_INCREMENT, 
	name varchar(20), 
	`desc` varchar(30)
);
drop table if exists student;
create table student( 
	id int primary key auto_increment, 
	sn int unique, 
	name varchar(20) not null,
	qq_mail varchar(20) default '[email protected]'
	foreign key (classes_id) references classes(id)
);

Among them, classes_id is the foreign key, which is associated with the security of the id field in the classes table

note

Class table and student table

  • Create: first create the main table (class table)
  • Insert: Insert the main table first
  • Delete: delete the child table (student table) first, or delete the main table first, but the premise is that the id of the main table is not associated

check constraint

drop table if exists test_user; 
create table test_user( 
	id int, 
	name varchar(20), 
	sex varchar(1), 
	check (sex ='男' or sex='女')
);

Indicates that when inserting data in the future, you can only insert sex as male or female.
However, if you insert something else, MySQL will not report an error.

Table design

One to one

A person has an ID

One to many

There are multiple students in a class

Many to many

One student can study multiple courses, and one course can be studied by multiple students