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

Classification

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

Example:

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

Defaults

Introduction

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'''

Example:

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,
	unique(ip,port)
);
insert into t5 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);

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'''

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 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
one-to-many
many to many
one to one
it doesn't matter

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

one-to-many

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
       answer:Can't
 	2.From the perspective of the department table
    	ask:Can a department correspond to multiple employees?
     	answer:Can	
	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)
);

When creating a table, you must first create the associated table
When entering table data, you must first enter the associated table
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

Programs Team

mysql database (field constraints)

What are field constraints

Classification

unsigned, zero-filled

unsigned unsigned

zerofill fills with 0

non empty

Example:

Defaults

Introduction

default constraint

Example:

unique value (key)

Single row unique

joint only

main building

Case 1:

Case 2:

self-increment

auto-increment feature

foreign key foreplay

The role of MySQL foreign keys:

judgment of relationship

one-to-many

Creation of foreign key fields

many-to-many relationship

one to one

Hot Tags