Non-empty, default value, unique value, primary key, self-increment, foreign key of field constraints

field constraints

unsigned, zero-filled

unsigned
	id int unsigned	Cancel the plus and minus signs
zerofill
	id int(5) zerofill Fill with 0 when there are less than 5 numbers

non empty

​ All terminal types can be empty by default without constraints

create table h1(
	id int,
    name varchar(32),
);
insert into h1(id) values(2);
insert into h1(name) values('jason');
insert into h1(name.id) values('tony',2);
create table h2(
	id int;
    name varchar(16) not null
);
insert into h2(id) values(1); Must report an error name Can not be empty
insert into h2(name) values('jason');
insert into h2(name) values(1,'');
insert into h2 values(2,null);

Defaults

create table h3(
	id int default 888,
    name varchar(32) default 'no name'
);
insert into h3(id) values(1);
insert into h3(name) values('jason');
insert into h3 values(2,'tony');

unique value

​ Unique in a single column

create table h4(
	id int unique,
    name vaechar(32) unique
);
insert into h4 values(1,'jason'),(2,'jason');
insert into h4 values(3,'kevin'),(4,'tony');

joint only

create table h5(
	id int,
    IP varchar(32),
    port int,
    unique(IP,port)
);
insert into h5 values(1,'127.0.0.1',8080),(2,'127.0.0.1',8081),(3,'127.0.0.2',8080);
insert into t5 values(4,'127.0.0.1',8080);

primary key

  1. From the constraint level alone, the primary key is equivalent to not null + unique (not empty and unique)
create table h6(
	id int primary key,
    name varchar(32)
);
insert into h6(name) values('jason');
insert into h6 values(1,'kevin');
insert into h6 values(1,'jerry');
  1. The InnoDB storage engine stipulates that all tables must have one and only one primary key (primary key is an important condition for organizing data, and primary key can speed up data query)
1.When there is no primary key and no other non-null and unique fields in the table
	InnoDB A hidden field will be used as the primary key of the table. Hiding means that the data query based on this table can only be searched row by row, and the speed is very slow.
2.When there is no primary key in the table but there are other non-empty and unique fields, the first field will be automatically upgraded to the primary key from top to bottom
create table h6(
	id int,
    age int not null unique,
    phone bigint not null unique,
    birth int not null unique,
    height int not null unique
);

'''
When we create a table, we should have a field to identify the uniqueness of the data, and this field is usually'id'(Numbering)field
id nid sid pid gid uid

create table uesrinfo(
	uid int primary key,
);
'''

self-increment

This constraint cannot appear alone and can only appear once in a table. It is mainly used together with the primary key.

create table h8(
	id int primary key,
    name varchar(32)
);

create table h9(
	id int primary key auto_increment,
    name varchar(64)
);

'''
Self-increment features:
	Self-increment will not fall back due to data deletion
	If you set a larger number, it will automatically increase according to the larger number
	
	If you want to reset the primary key value of a table, you can use truncate t9; Clear the table data and reset the primary key
'''

foreign key foreplay

We need an employee form
	id	name	age	dep_name	dep_desc
1.The meaning of the expression is not clear(Is it an employee or a department?)   doesn't matter
2.Accessing data is too redundant(waste of storage space)	   doesn't matter
3.Data scalability is extremely poor			 	   can not be ignored

Divide the above table into two

id	name	age

id	dep_name	dep_desc
 The above three problems are all solved, but there is no relationship between employees and departments

Foreign key field: a field used to identify the relationship between data and data

judgment of relationship

Table relationship and data relationship actually have the same meaning, but there are differences in terms of knowledge

There are four types of relationships: one-to-many, many-to-many, one-to-one, and no relationship

The judgment of relationship can adopt the principle of 'empathy'

one-to-many relationship

An employee table and department table as an example

  1. First stand in the perspective of the employee table

    Q: Can one employee correspond to multiple departments?

    Answer: Of course not

  2. From the perspective of the department table

    Q: Can a department correspond to multiple employees?

    Answer: yes

Conclusion: One can and the other cannot, then the relationship is 'one-to-many' for the 'one-to-many' relationship foreign key field is on the 'many' side

Creation of foreign key fields

Tip: first define a table with ordinary fields, and then consider adding foreign key fields

create table emp(
	id not primary key auto_increment,
    name varchar(32),
    age int,
    dep_id int,
    foreign key(dep_id) references dep(id)
);

create table dep(
	id not primary key auto_increment,
    dep_name varchar(32),
    dep_desc varchar(64)
);
1.When creating a table, you must first create the associated table
2.When entering table data, you must first enter the associated table
3.Foreign key fields cannot be modified or deleted when modifying data

There are simplified operation measures for 3: cascading update and cascading delete
create table emp1(
	id int primary key auto_increment,
    name varchar(32),
    age int,
    dep_id int,
    foreign key(dep_id) references dep1(id)
    on update cascade
    on delete cascade
);

create table dep1(
	id int primary key auto_increment,
    dep_name varchar(32),
    dep_desc varchar(64)
);

Foreign keys are actually strong coupling and do not meet the characteristics of decoupling
	So many times, when there are many tables in the actual project, we may not use foreign keys, but use code to establish logical relationship

many-to-many relationship

Take book table and author table as an example
1. First stand in the perspective of the book table
Q: Can one author correspond to multiple volumes?
Answer: yes

  1. From the perspective of the author table

    Q: Can one author correspond to multiple books

    Answer: yes

Conclusion: Both are ok. The relationship is 'many to many'

For 'many-to-many', it cannot be created directly in the table, and a third relational table needs to be created

create table book(
	id int primary key auto_increment,
    title varchar(32),
    price float(5,2),
);

create table author(
	id int primary key auto_increment,
    name varchar(32),
    phone bigint
);

create table book2author(
	id int primary key auto_increment,
   author_id int,
    foreign key(author_id) references author(id)
    on update cascade
    on delete cascade,
   book_id int,
    foreign key(book_id) references book(id)
    on update cascade
    on delete cascade
);

one-to-one relationship

Take the user table and user details table as an example

  1. First stand in the perspective of the user table

    Q: Can one user correspond to multiple user details?

    Answer: no

  2. From the perspective of the user details table

    Q: Can one user detail correspond to multiple users?

    Answer: no

Conclusion: both are ok Relationship is 'one to one' or no relationship

For the 'one-to-one' foreign key field, it can be built on either side, but it is recommended to be built in the table with high query frequency

create table user(
	id int primary key auto_increment,
    name varchar(32),
    dateil_id int unique,
    foreign key(datail_id) references userdetail(id)
    on update cascade
    on delete cascade
);

create table userdertail(
	id int primary key auto_increment,
    phone bigint
);

Posted by jlarson on Thu, 24 Nov 2022 12:21:05 +0300