1, Restraint·
1. Primary key constraint
We can add constraints to a data table of a data table so that it can uniquely identify a data table. After being constrained, the data is not repeated and not unique: primary key
mysql> use test;
mysql> create table number(
-> id int primary key,
-> name varchar(20)
-> );
Query OK, 0 rows affected (0.09 sec)
mysql> describe number;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.03 sec)
You can notice that the key below becomes PRI, indicating that it is a primary key element.
When we add a duplicate id, an error will be reported.
mysql> insert into number value(1,'Zhang San'); Query OK, 1 row affected (0.03 sec) mysql> insert into number value(1,'Zhang San'); ERROR 1062 (23000): Duplicate entry '1' for key 'number.PRIMARY'
Another Zhang San can only be added if our id is changed without repetition
mysql> select * from number; +----+------+ | id | name | +----+------+ | 1 | Zhang San | | 2 | Zhang San | +----+------+ 2 rows in set (0.01 sec)
In addition, the data of the primary key constraint cannot be empty
mysql> insert into number value(null,'Zhang San'); ERROR 1048 (23000): Column 'id' cannot be null
2. Primary key constraint - Union primary key
It is defined at the end with primary key() or after each variable
mysql> create table number2(
-> id int,
-> name varchar(20),
-> primary key(id,name)
-> );
Query OK, 0 rows affected (0.07 sec)
mysql> desc number2; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int | NO | PRI | NULL | | | name | varchar(20) | NO | PRI | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec)
mysql> insert into number2 value(1,'Zhang San'); Query OK, 1 row affected (0.02 sec)
mysql> insert into number2 value(2,'Li Si'); Query OK, 1 row affected (0.01 sec)
mysql> select *from number2; +----+------+ | id | name | +----+------+ | 1 | Zhang San | | 2 | Li Si | +----+------+
If you add another piece of data with the same ID and name, an error will be reported
mysql> insert into number2 value(2,'Li Si'); ERROR 1062 (23000): Duplicate entry '2-Li Si' for key 'number2.PRIMARY'
But if there is a difference between id and name, it's OK
mysql> insert into number2 value(1,'Li Wu'); Query OK, 1 row affected (0.02 sec) mysql> select *from number2; +----+------+ | id | name | +----+------+ | 1 | Zhang San | | 1 | Li Wu | | 2 | Li Si | +----+------+ 3 rows in set (0.00 sec)
Of course, neither id nor name can be empty
mysql> insert into number2 value(null,'Li Si'); ERROR 1048 (23000): Column 'id' cannot be null mysql> insert into number2 value(2,null); ERROR 1048 (23000): Column 'name' cannot be null
3. Self increasing constraint
Add auto after the data_ Increment enables the id to be added without the user transmitting it
mysql> create table user3(
-> id int primary key auto_increment,
-> name varchar(15)
-> );
Query OK, 0 rows affected (0.08 sec)
mysql> desc user3
-> ;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(15) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)
mysql> insert into user3 (name) values('Zhang San');
Query OK, 1 row affected (0.02 sec)
mysql> select * from user3;
+----+------+
| id | name |
+----+------+
| 1 | Zhang San |
+----+------+
1 row in set (0.00 sec)
It can be seen that we did not pass in the id, but took the initiative to help us complete it
mysql> insert into user3 (name) values('Li Si');
Query OK, 1 row affected (0.03 sec)
mysql> select * from user3;
+----+------+
| id | name |
+----+------+
| 1 | Zhang San |
| 2 | Li Si |
+----+------+
2 rows in set (0.00 sec)
When we pass in another data, the id will increase automatically
4. If we forget to add a primary key constraint when creating a data table, we can also use: 1) alter table add (constraint) (data to be constrained)
2) Use modify: alter table to modify (data to be constrained) (data type + data constraint type)
mysql> create table use4(
-> id int,
-> name varchar(15)
-> );
Query OK, 0 rows affected (0.07 sec)
mysql> desc use4;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(15) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
In the above, we do not impose any constraints on the id
1) When we use alter... add for the id, we can see that the id has been added to the primary key
mysql> alter table use4 add primary key(id); Query OK, 0 rows affected (0.09 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc use4; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int | NO | PRI | NULL | | | name | varchar(15) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec)
2) When we use modify for the id, we can see that the id has also been added to the primary key
mysql> alter table use4 modify id int primary key; Query OK, 0 rows affected (0.11 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc use4; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int | NO | PRI | NULL | | | name | varchar(15) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec)
5. When we want to delete the primary key, we can use:
alter table drop
mysql> alter table use4 drop primary key; Query OK, 0 rows affected (0.10 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc use4; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int | NO | | NULL | | | name | varchar(15) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec)
6. Unique constraint unqiue
If the key value is uni, it means that the data cannot be repeated!
mysql> alter table use4 add unique(name); Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc use4; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int | NO | PRI | NULL | | | name | varchar(15) | YES | UNI | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec)
In this way, we can add unique constraints to both id and name. In this way, when adding new data, the same id and name cannot be used, which is different from the primary key joint constraint!
mysql> insert into use4 value(1,'Zhang San'); Query OK, 1 row affected (0.02 sec) mysql> insert into use4 value(2,'Li Si'); Query OK, 1 row affected (0.02 sec)
mysql> select * from use4; +----+------+ | id | name | +----+------+ | 1 | Zhang San | | 2 | Li Si | +----+------+ 2 rows in set (0.00 sec) mysql> insert into use4 value(1,'Li Wu'); ERROR 1062 (23000): Duplicate entry '1' for key 'use4.PRIMARY' mysql> insert into use4 value(3,'Li Wu'); Query OK, 1 row affected (0.02 sec)
7. Delete the unique constraint primary key: alter table (data table name) drop index (data name);
mysql> alter table use4 drop index id; Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc use4; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int | NO | PRI | NULL | | | name | varchar(15) | YES | UNI | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec)
8. Non NULL constraint
When creating a data table, you can add not null after the data
The function is that the data cannot be empty.
9. Default constraint
The simple understanding is to set a default value for the data. When you do not input data, the default value is used by default.
mysql> create table use5(
-> id int,
-> name varchar(15),
-> age int default 10
-> );
Query OK, 0 rows affected (0.13 sec)
mysql> desc use5; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int | YES | | NULL | | | name | varchar(15) | YES | | NULL | | | age | int | YES | | 10 | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
mysql> insert into use5 (id,name) value(1,'Zhang San'); Query OK, 1 row affected (0.02 sec) mysql> insert into use5 value(2,'Li Si','15'); Query OK, 1 row affected (0.02 sec)
mysql> select * from use5; +------+------+------+ | id | name | age | +------+------+------+ | 1 | Zhang San | 10 | | 2 | Li Si | 15 | +------+------+------+ 2 rows in set (0.01 sec)
It can be seen that if we don't fill in Zhang San's age, it defaults to 10, while Li Si's age is the data we fill in.
Key points: 10 foreign key constraints
Foreign key constraints generally design more than two tables: parent table and child table
Parent table:
mysql> create table classes(
-> id int primary key,
-> name varchar(20)
-> );
Query OK, 0 rows affected (0.07 sec)
Sub table:
mysql> create table students(
-> id int primary key,
-> class_id int,
-> foreign key(class_id) references classes(id)
-> );
Query OK, 0 rows affected (0.08 sec)
Add data to parent table:
mysql> insert into classes values(1,'Class one'); Query OK, 1 row affected (0.01 sec) mysql> insert into classes values(2,'Class two'); Query OK, 1 row affected (0.02 sec) mysql> insert into classes values(3,'Class three'); Query OK, 1 row affected (0.02 sec) mysql> insert into classes values(4,'Class four'); Query OK, 1 row affected (0.02 sec) mysql> select *from classes; +----+------+ | id | name | +----+------+ | 1 | Class one | | 2 | Class two | | 3 | Class three | | 4 | Class four | +----+------+ 4 rows in set (0.01 sec)
Add data to sub table:
mysql> insert into students values(1001,1); Query OK, 1 row affected (0.02 sec) mysql> insert into students values(1002,2); Query OK, 1 row affected (0.02 sec) mysql> insert into students values(1003,3); Query OK, 1 row affected (0.02 sec) mysql> insert into students values(1004,4); Query OK, 1 row affected (0.02 sec) mysql> select *from students; +------+----------+ | id | class_id | +------+----------+ | 1001 | 1 | | 1002 | 2 | | 1003 | 3 | | 1004 | 4 | +------+----------+ 4 rows in set (0.00 sec)
Note: 1. There is no data value in the main table classes, which cannot be used in the sub table.
mysql> insert into students values(1005,5); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`students`, CONSTRAINT `students_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `classes` (`id`))
Here, because there is no value of 5 shifts in the main table, an error will be reported when the sub table wants to add 5 shifts
2. Records in the main table cannot be deleted when referenced by sub tables
mysql> delete from classes where id=4; ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`students`, CONSTRAINT `students_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `classes` (`id`))