当前位置：网站首页>MySQL table constraints and table design

Table constraints are created when a table is created , Design ⼀ The constraints of these tables ,⽤ To ensure the legitimacy and correctness of the data .
for example ⼀ Only one account can be bound ⼀ individual ⼿ immediately , that ⼿ The machine number cannot be repeated , Cannot be bound by multiple accounts to make ⽤, So it should be set to only ⼀ constraint .⽽ The primary key is ⽤ To identify the data , Therefore, it cannot be NULL, It can't be empty , So he needs to set ⾮ Empty constraint or （ Primary key constraint , The primary key constraint cannot be NULL）.

1.2 Why do you need table constraints

Like the law is ⽤ To regulate ⼈ Our correct ⾏ For the purpose of ⼀ sample , Table constraints are also used to regulate programmers to make ⽤ Tabular , But table constraints are preset , After setting, all plug-ins will be ⼊ And modify ⽴ namely ⽣ effect ,⽐ Such as ⾮ After the NULL constraint is set , If added and modified as NULL The value will report an error , This is slightly different from the law . If there is no table constraint , Will cause incorrect data , from ⽽ Lead to program or real business ⽆ Law promotion and enforcement ⾏.

1.3 Common constraints

often ⻅ The table constraints are as follows ：

constraint	explain
NOT NULL	⾮ NULL constraint , Indicates that a column cannot store NULL value
UNIQUE	only ⼀ constraint , Ensure that every... Of a train ⾏ There must be only ⼀ Value
PRIMARY KEY	Primary key constraint , Make sure that a column （ Or a combination of two or more columns ） Have only ⼀ identification , be conducive to It's easier and faster to find... In the table ⼀ A specific record .
FOREIGN KEY	Foreign key constraints , Guarantee ⼀ The data in one table matches another ⼀ Referential integrity of values in a table .
CHECK	Check constraint , Ensure that the values in the column meet the specified conditions . about MySQL database , Yes CHECK⼦ Sentence into ⾏ analysis , But ignore CHECK⼦ sentence .
AUTO_INCREMENT	⾃ Increasing constraint , insert ⼊ This field can be ignored , This field will ⾃⼰ Increase the stored value . The default is 1 Start , Each increment 1.
DEFAULT	Default constraint , Specifies the default value when the column is not assigned a value .

1.3.1 Non empty constraint not null

grammar ：

create table table_name( Field name   data type  not null [,...]);

for example , to id and name Add a non NULL constraint ：

Insert null value , Will report a mistake ：

View non empty constraints ：

Use desc table_name To query ,NO Indicates that a non NULL constraint is added ,YES Indicates that no non NULL constraint has been added .

1.3.2 Unique constraint unique

grammar ：

create table table_name ( Field name   data type  unique [,...]);

for example , to id Add a unique constraint ：

Add two id Same data ：

An error occurs , Unable to add .

Be careful ：

A table can have one or more unique constraints ;
Unique constraint fields can be inserted NULL value ;
Unique constraint field NULL Multiple can be inserted .

View the unique constraints of a table ：

1.3.3 Primary key constraint primary key

The primary key is OK ⽤ To express ⼀ Data in a table represents a voucher , For example, for “⼈” For this table , only ⼀ Your ID card can be used as the primary key to represent this ⼈,“ full name ” No ⾏, Because the name may repeat .

The characteristics of primary keys ：

A primary key can consist of multiple fields or a single field .
The primary key cannot be empty and unique ⼀.
⼀ There can only be... In a table ⼀ Primary keys .

grammar 1： Independent primary key

create table table_name ( Field name   data type  primary key [,...]);

Example ：

grammar 2： Combined the primary key

create table table_name (
     Field 1  data type ,
     Field 2  data type 
    [,...],
    primary key( Field 1, Field 2[,...])
);

Example ：

Primary key constraint VS only ⼀ constraint ：

Primary key constraint ⼀ A table can only have ⼀ individual ,⽽ only ⼀ Constraints can have multiple ;
Primary key constraint cannot have null value ,⽽ only ⼀ Constraints can have null（ only ⼀ There can be more than one index null）.

1.3.4 Foreign key constraints foreign key

Foreign keys ⽤ Associated with other tables Primary key or only ⼀ key , grammar ：

foreign key ( Field name ) references  Main table ( Column )

Example ： Create class table classes,id Primary key ：

Create class table , Have use MySQL When a keyword is used as a field , Need to use `` To mark

Create student table student, One student corresponds to one class , One class corresponds to more than one student . Use id Primary key ,classes_id For foreign key , Associate the class table id

1.3.5 Check constraint check（ understand ）

grammar ：

check (< Check constraint >)

Example ：

Special instructions ： Check that the constraint is MySQL 8.0.16 Can be used , stay MySQL 8.0.15 Not used before .

1.3.6 Self increasing constraint auto_increment

grammar ：

create table table_name(
    id int primary key auto_increment,
    name varchar(250)
);

Example ：

Insert two name：

Use show create table Table name You can view the current self increment ：

When inserting... Into a self incrementing column null when , Its execution logic and no setting are the same ：

Auto increment can also be manually specified ：

⾃ Value added can be done when creating tables ⼿ Move to specify ：

Modify self increment ：

send ⽤ alter table table_name auto_increment=n, Modifiable ⾃ Value added is n

Be careful ：

There can only be one self incrementing column in a table ;
⾃ The field type of added column can only be integer type （TINYINT、SMALLINT、INT、BIGINT etc. ）;
auto_increment Must cooperate key ⼀ Start to make ⽤, This key It can be primary key,foreign key, without key You're going to report a mistake ;
only ⼀ Columns can be ⾃ Addition ：
auto_increment The value of can only be set ⽐⽬ Pre stored most ⼤ value ⼤, Otherwise, the setting will not ⽣ effect .

delete and truncate Reset auto increment verification ：

delete Delete Can't Reset self increment ：

truncate Delete Meeting Reset self increment ：

1.3.7 Default constraint default

The default constraint is the default value when no value is assigned to the column , The grammar is as follows ：

 Field   data type  default  The default value is

Example ：

1.3.8 insert ... select

2. Table design

2.1 Three paradigms for database design

2.1.1 The first ⼀ normal form ( Make sure that each column remains atomic )

The first ⼀ Paradigm is the most basic paradigm . If all field values in the database table are non decomposable ⼦ value , It means that the database table is full ⾜ No ⼀ normal form . The first ⼀ The reasonable compliance of the paradigm needs to be determined according to the actual needs of the system .⽐ For example, some database systems need ⽤ To “ Address ” This attribute , Would have been “ Address ” The property is designed to ⼀ The fields of a database table are ⾏. But if the system often accesses “ Address ” Attribute “ City ” part , then ⾮ To put “ Address ” This property is subdivided into provinces 、 City 、 Detailed address and other parts ⾏ Storage , In this way, in the right address, some ⼀ Some operations will ⾮ often ⽅ then . This design is full ⾜ The third part of the database ⼀ normal form , As shown in the following table .

As shown in the table above ⽤ The user information follows section ⼀ The requirements of paradigm , This is right ⽤ Household emissary ⽤ The city enters ⾏ When you classify, you ⾮ often ⽅ then , Also mentioned ⾼ The performance of the database .

2.1.2 The first ⼆ normal form ( Make sure that each column in the table is related to the primary key )

The first ⼆ Paradigm in Chapter ⼀ On the basis of paradigm ⼀ layer . The first ⼆ The paradigm needs to ensure that every in the database table ⼀ Columns are related to primary keys ,⽽ It cannot be related to only one of the primary keys ⼀ Part of it （ It's mainly for joint primary key ⽽⾔）. That is to say ⼀ Database tables ,⼀ Only... Can be saved in a table ⼀ Species data , Multiple data cannot be saved in the same ⼀ In a database table .
⽐ If you want to design ⼀ An order information table , Because there may be many kinds of goods in the order , Therefore, the order number and the product number should be used as the joint primary key of the database table , As shown in the following table .

This will produce ⽣⼀ A question ： In this table, the order number and product number are used as the joint primary key . In this way, the commodity name in the table 、 Company 、 Commodity price and other information are not related to the primary key of this table ,⽽ It's just related to the item number . So here ⾥ In violation of article ⼆ Design principles of paradigm .⽽ If you put this order information into ⾏ Split , Separate the product information into another ⼀ In a list , Separate the order quantity table into another ⼀ In a list , Just ⾮ Often perfect . As shown below .

This design , In the very ⼤ To a lesser extent ⼩ The redundancy of the database . If you want to get the product information of the order , send ⽤ The commodity number can be queried in the commodity information table .

2.1.3 Third normal form ( Make sure that each column is directly related to the primary key column , Not indirectly )

The third paradigm needs to ensure that every... In the data table ⼀ The column data is directly related to the primary key ,⽽ Not indirectly .⽐ Such as in the design ⼀ When creating an order data sheet , You can use the customer number as ⼀ Create a foreign key and order table ⽴ Corresponding relationship .⽽ No other information about the customer can be added to the order form （⽐ Such as name 、 Company, etc ） Field of .
as follows ⾯ The design shown in these two tables is ⼀ Geman ⾜ Database tables of the third paradigm . It doesn't conform to the third paradigm ：

Correct table structure ：

In this way, when querying the order information , You can make ⽤ Customer number to quote ⽤ Records in the customer information table , You don't have to enter... Multiple times in the order information table ⼊ Content of customer information , reduce ⼩ Data redundancy .

2.2 Table relations

There are three relationships between tables ：

⼀ Yes ⼀：⼀ Individual studies ⽣ Corresponding ⼀ A student id ;
⼀ For more than ：⼀ A class contains multiple students ⽣（⽤ Two tables indicate ）;
Many to many ：⼀ In this table ⼀ One piece of data can be mapped to another ⼀ Multiple pieces of data in a table , The opposite is true , This relationship is called many to many ,⼀ like ⽤ Three tables represent .

one-on-one ：