mysql database (field constraints)

What are field constraints

  • Field constraints are to set a rule for the content of the field, and we have to follow the rules
constraint describe keywords
not-null constraint Restrict the data of this field to not be null not null
unique constraint Ensure that all data in this field is unique and not repeated unlque
default constraints When saving data, if the value of this field is not specified, the default value will be used default
primary key constraint The primary key is the unique identifier of a row of data, which requires non-empty and unique primary key
foreign key constraints It is used to establish a connection between the data of the two tables to ensure the consistency and integrity of the data forelgn key


field name field meaning Field Type Restrictions constraint keyword
id unique ID int Primary key, and auto-increment primary key,auto_increment
name Name varchar(10) Can not be empty, and unique not null, unlque
age age int Greater than 0 and less than or equal to 120 check
status state char(1) If this value is not specified, it defaults to 1 default
gender gender char(1) none none

unsigned, zero-filled

unsigned unsigned

mysql> create table t17(
    -> id int unsigned
    -> );
Query OK, 0 rows affected (0.19 sec)

Inserting numbers cannot be signed

zerofill fills with 0

mysql> create table t18(
    -> id int zerofill
    -> );
Query OK, 0 rows affected (0.22 sec)

Zero padding will automatically use unsigned bits; zero padding means that the number of digits is fixed, and if the length of the value is less than the length of the field type, use 0 to pad

non empty

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

<field name><type of data> NOT NULL


create table t1(
	id int,
	name varchar(16)
insert into t1(id) values(1);
insert into t1(name) values('jason');
inser into t1(name,id) values('kevin',2);

create table t2(
	id int,
	name varchar(16) not null
insert into t2(id) values(1);
insert into t2(name) values('jason');
insert into t2 values(1,'');
insert into t2 values(2,null);

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



The full name of Default is "Default Constraint", which is used to specify the default value of a column. When inserting a new record in the table, if a field is not assigned a value, the system will automatically insert a default value for this field

default constraint

<field name> <type of data> DEFAULT <Defaults>;
'''Usually used together with non-empty'''


create table t3(
	id int default 666,
	name varchar(16) default 'anonymous'
insert into t3(id) values(1);
insert into t3(name) values('jason');
insert into t3values(2, 'kevin');

unique value (key)

Single row unique

<field name><type of data> unique
create table t4(
	id int unique,
	name varchar(32) unique
insert into t4 values(1, 'jason'),(2, 'jason');

joint only

create table t5(
	id int,
	ip varchar(32),
	port int,
insert into t5 values(1,'',8080),(2,'',8081),(3,'',8080);
insert into t5 values(4,'',8080);

main building

Case 1:

From the constraint level alone, the primary key is equivalent to not null + unique (not empty and unique)

<field name> <type of data> PRIMARY KEY [Defaults]
create table t6(
	id int primary key,
	name varchar(32)
insert into t6(name) values('jason');
insert into t6 values(1,'kevin');
insert into t6 values(1, 'jerry');

Case 2:

[CONSTRAINT <constraint name>] PRIMARY KEY [field name]
  • The InnoDB storage engine stipulates that all tables must have one and only one primary key (primary key is an important condition for organizational design and primary key and primary key can speed up data query)

1. When there is no primary key or other non-empty-cut unique fields in the table
InnoDB will use a hidden field 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, then the first field will be automatically upgraded to the primary key from top to bottom

create table t7(
	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 the 'id' (number) field
create table userinfo(
	uid int primary key,
'''must write code'''


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

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

create table t9(
	id int primary key auto_increment,
	name varchar(32)

auto-increment feature

  • Auto-increment will not fall back due to data deletion, auto-increment will always move forward
    If you set a larger number yourself, it will automatically increase according to the larger number in the future

foreign key foreplay

The role of MySQL foreign keys:

  • Maintain data consistency, integrity, the main purpose is to control the data stored in the foreign key table. To associate the two tables, the foreign key can only refer to the value of the column in the foreign table
We need an employee form
	id	name	age		dep_name	dep_desc

1.Table semantics are ambiguous(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

judgment of relationship

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

There are four kinds of relationships
many to many
one to one
it doesn't matter

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


Take the employee table and department table as an example
	1.First stand in the perspective of the employee table
    	ask:Can one employee correspond to multiple departments
 	2.From the perspective of the department table
    	ask:Can a department correspond to multiple employees?
	in conclusion:One can and the other cannot, then the relationship is'one-to-many'
	against'one-to-many'Relational foreign key fields built in'many'party of

Creation of foreign key fields

  • Tip: first define a table with ordinary fields, and then consider adding foreign key fields
create table emp(
	id int primary key auto_increment,
 	name varchar(32),
 	age int,
 	dep_id int,
	foreign key(dep_id) references dep(id)

create table dep(
	id int 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 simplification measures for 3 >>>: cascading update 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)

Summary: Foreign keys are actually strong coupling and do not meet the characteristics of decoupling
So in many cases, when there are many tables in actual projects, we may not use
Use foreign keys instead of using code to establish relationships at the logical level

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 a book correspond to multiple authors?
Answer: yes
2. Standing in 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

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, the relationship is 'one-to-one' or there is 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),
 	detail_id int unique,
  	foreign key(detail_id) references userdetail(id)
  	on update cascade
  	on delete cascade

create table userdetail(
	id int primary key auto_increment,
  	phone bigint

Similar to one-to-many, plus a unique condition

Posted by jfeather on Thu, 24 Nov 2022 18:12:24 +0530