catalogue

1, MySQL PRIMARY KEY

Set primary key constraint when creating table

Set federated primary key when creating table

Delete and modify primary key constraints

II. MySQL FOREIGN KEY constraint and UNIQUE constraint

Set foreign key constraints and unique constraints when creating tables

3, MySQL CHECK constraint (CHECK)

Delete check constraint

IV. constraints in MySQL view table

1, MySQL PRIMARY KEY (PRIMARY KEY)

The complete name of PRIMARY KEY is "PRIMARY KEY constraint" MySQL The most frequently used constraint in. Generally, in order to facilitate the DBMS to find the records in the table faster, a primary key will be set in the table.

When using a primary key, you should pay attention to the following points:

• Only one primary key can be defined for each table.
• The primary key value must uniquely identify each row in the table and cannot be NULL, that is, there cannot be two rows of data with the same primary key value in the table. This is the principle of uniqueness.
• A field name can only appear once in the federated primary key field table.
• A federated primary key cannot contain unnecessary extra fields. When a field of the joint primary key is deleted, if the primary key composed of the remaining fields still meets the uniqueness principle, the joint primary key is incorrect. This is the minimization principle.

Set primary key constraint when creating table

In the CREATE TABLE statement, use the PRIMARY KEY keyword to specify the PRIMARY KEY.

create table tt1(
id int(10),
sex enum('male','female')DEFAULT'male',
teacher VARCHAR(5),
PRIMARY KEY(teacher)
);
insert into tt1 (id,teacher)VALUE(202201,'Lucy');
-- Because the primary key is teacher It is unique, so the insertion is not successful
-- insert into tt1 (id,teacher)VALUE(202202,'Lucy');
SELECT * FROM tt1;

Set federated primary key when creating table

The so-called joint primary key means that the primary key is composed of multiple fields in a table.
For example, when setting the student course selection data table, do you use the student number as the primary key or the course number as the primary key? If the student number is used as the primary key, a student can only choose one course. If the course number is used as the primary key, only one student can choose a course. Obviously, these two situations are not in line with the actual situation.
In fact, when designing a student's course selection table, it is limited that a student can only choose the same course once. Therefore, the student number and course number can be put together as the primary key, which is the joint primary key.

Note: when the primary key is composed of multiple fields, you cannot declare the primary key constraint directly after the field name.

create table tt2(
id int(10),
sex enum('male','female')DEFAULT'male',
teacher VARCHAR(5),
PRIMARY KEY(id,teacher)
);
insert into tt2 (id,teacher)VALUE(202201,'Lucy');
insert into tt2 (id,teacher)VALUE(202202,'Marry');
SELECT * FROM tt2;

Delete and modify primary key constraints

Because there can only be one primary key constraint in a table, you can delete a primary key constraint in a table without specifying the primary key name.

Generally, when you want to set the primary key constraint of a field in the table when modifying the table, you should ensure that the value of the field set as the primary key constraint cannot be duplicate, and that it is not empty. Otherwise, the primary key constraint cannot be set.

-- Delete primary key add primary key
alter table tt1 drop primary key;
alter table tt1 add primary key(teacher);
-- -- If there is duplicate data, the primary key constraint cannot be added

II. MySQL FOREIGN KEY constraint and UNIQUE constraint

MySQL FOREIGN KEY constraint is a special field of a table, which is often used with primary key constraint. For two associated tables, the table with the primary key in the associated field is the master table (parent table), and the table with the FOREIGN KEY is the slave table (child table).
It is used to establish the consistency between the master table and the slave table. For example, in a fruit stall, there are only four kinds of fruits: apple, peach, plum and watermelon. When you come to the fruit stall to buy fruit, you can only choose apple, peach, plum and watermelon. Other fruits cannot be purchased.

When defining foreign keys, you need to follow the following rules:

• The main table must already exist in the database or be the table currently being created. In the latter case, the master table and the slave table are the same table. Such a table is called a self reference table, and this structure is called self reference integrity.
• You must define a primary key for the primary table.
• The primary key cannot contain null values, but null values are allowed in foreign keys. In other words, as long as each non null value of the foreign key appears in the specified primary key, the content of the foreign key is correct.
• Specify the column name or combination of column names after the table name of the main table. This column or combination of columns must be the primary key or candidate key of the main table.
• The number of columns in the foreign key must be the same as that in the primary key of the main table.
• The data type of the column in the foreign key must be the same as that of the corresponding column in the primary key of the main table.

MySQL Unique Key means that the values of fields in all records cannot be repeated. For example, after adding a uniqueness constraint to the id field, the id value of each record is unique and cannot be repeated. If the id value of one record is' 0001 ', the id value of another record cannot appear in the table.
Unique constraints are similar to primary key constraints in that they can ensure the uniqueness of columns. The difference is that there can be multiple unique constraints in a table, and the columns with unique constraints are allowed to have null values, but there can only be one null value. There can only be one primary key constraint in a table, and null values are not allowed. For example, in the user information table, in order to avoid duplicate user names in the table, the user name can be set as a unique constraint.

Set foreign key constraints and unique constraints when creating tables

create table work(
id int(10)auto_increment comment'Teacher number',
sname varchar(10) not null comment'Teacher name',
kname varchar(10) UNIQUE comment'curriculum',-- As a referenced foreign key, it must be unique unique of
primary key(id,kname)
);
insert into work(id,sname,kname)VALUE(1,'Lucy','java');
insert into work(sname,kname)VALUE('Mary','c++');
select * from work;

result:

--  CONSTRAINT Foreign key name FOREIGN KEY (Listing) REFERENCES Main table name(Primary key name of the primary table)
create table work2(
--  idx Associate to table as foreign key work Primary key of id
id int auto_increment comment'Student number',
idx int (10) comment'Corresponding teacher number',
sname varchar(10) not null comment'Student name',
primary key(id),
CONSTRAINT fk_dept1 FOREIGN KEY (idx) REFERENCES work(id)
);
-- After the above statement is executed successfully,In table work2 Added the name fk_dept1 Foreign key constraint for, foreign key name is  idx ，It depends on the table work Primary key of id. 
insert into work2(id,idx,sname)value(1,1,'Zhang 1');
insert into work2(idx,sname)value(1,'Zhang 2');
insert into work2(idx,sname)value(2,'Zhang 3');
insert into work2(idx,sname)value(2,'Zhang 4');
-- insert into work2(idx,sname)value(3,'Zhang 5'); Will report an error,because work The primary key in the depends on only 1,2 Two values
select * from work2;

Results:

-- When modifying the data table, the syntax format of adding foreign key constraints is as follows:
-- ALTER TABLE Data table name ADD CONSTRAINT Foreign key name FOREIGN KEY(Listing) REFERENCES Main table name(Listing);
alter table work2 add kcname varchar(10) comment'curriculum';
alter table work2 add CONSTRAINT fk_dept2 FOREIGN KEY(kcname) REFERENCES work(kname);
-- insert data
UPDATE work2 set kcname='java' where id=1;
UPDATE work2 set kcname='java' where id=2;
select * from work2;

Results:

3, MySQL CHECK constraint (CHECK)

MySQL CHECK constraint is a means to CHECK the validity of field values in the data table, which can be realized through CREATE TABLE or ALTER TABLE statements. When setting CHECK constraints, you should set them according to the actual situation, which can reduce the input of invalid data.

The CHECK keyword is used for checking constraints. The specific syntax format is as follows:

Check < expression >

Delete check constraint

When modifying a table, the syntax format of deleting a check constraint is as follows:

Alter table < data table name > drop constraint < check constraint name >;

IV. constraints in MySQL view table

The constraint syntax format in the view data table is as follows:

Show create table < data table name >;

show CREATE table work2;

result: