This is the sixth day of my participation in the August More text Challenge. For details, see: August More Text Challenge

What is a foreign key constraint

MySQL’s foreign key constraint is used to establish a link between two tables where one table changes and the other changes. From this point of view, it is mainly to ensure the consistency and integrity of the table data.

When two tables are associated by a foreign key, the table in which the primary key is located is the primary table (also known as the parent table), and the table in which the foreign key is located is the secondary table (also known as the child table). When defining a foreign key, follow the following rules:

The parent table must already exist in the database or be a table currently being created. In the latter case, the parent table and the child table are the same table, such a table is called a self-referential table, and this structure is called self-referential. You must define a primary key for the parent table. 3. Primary keys cannot contain null values, but foreign keys are allowed to contain null values. That is, the contents of a foreign key are correct as long as each non-null value of the foreign key appears in the specified primary key. The number of columns in the foreign key must be the same as the number of columns in the primary key of the parent table.

For example, if you have two tables, one is the user table, the other is the order table, if the two tables are not associated, two things will happen:

  1. If I delete the user from the user table, then the data in the order table, which is related to that user, will be headless and incomplete.
  2. If I insert something in the order table, it’s in the user table, and there’s no user for that order. So the data is incomplete.

So what we’re going to do is we’re going to introduce the idea of a foreign key so that if you have a foreign key in these two tables, you can either not let the user delete the data, or if you delete the user, you can use the foreign key to delete the data in the order table as well, so that the data is still intact.

Syntax for foreign key constraints:

Create foreign key Constraint foreign key: Data integrity is checked for each insert or update.

Create table; create table;

Create table SQL > create table SQL > create table SQL > create table SQL > create table SQL > create table SQL > create table SQL... ..) [ON DELETE CASCADE ] [ON UPDATE CASCADE ] )Copy the code

Description of parameters: RESTRICT: declines to delete or update the parent table. CASCADE: Delete or update rows from the parent table and automatically delete or update matched rows in the child table. ON DELETE CASCADE and ON UPDATE CASCADE On update cascade, on delete cascade, means that when you update or delete the primary key table, the foreign key table will also be updated or deleted.

Simplified syntax:

Foreign key Field of the current table References External table name (associated field) ENGINE = innoDBCopy the code

Note: The creation is successful only when the following four conditions are met:

  1. Ensure that the referenced tables and fields exist.
  2. The fields that make up the foreign key are indexed.
  3. You must specify innoDB as the storage ENGINE using ENGINE.
  4. Foreign key fields and associated fields must have the same data type.

For example, let’s create a database that contains a user information table and an order table

mysql> create table `user`(id int(11) not null auto_increment,name varchar(50) not null default ' ', sex int(1)  not null default '0', primary key(id))ENGINE=innodb; Query OK, 0 rows affected (0.02sec)Copy the code

Note: When creating an order table, if the table name is an SQL keyword, use backquotes to create the order table

mysql> create table `order1`(order_id int(11) auto_increment, u_id int(11) default '0'. username varchar(40),money int(11),primary key(order_id),index(u_id),foreign key order_f_key(u_id) references user(id) on delete cascade on update cascade)type=innodb;
Copy the code

Query OK, 0 rows affected (0.00 sec)

Note: On delete cascade on update CASCADE Adds the cascade to delete and update. Ensure that the ID field exists in the referenced table user. The field U_id that forms the foreign key is indexed. You must specify innoDB as the storage engine using type.

Foreign key fields and associated fields must have the same data type. Insert test data

mysql> insert into `user`(name,sex)values('zr', 2), ('wld', 1), ('zmedu', 1); Query OK, 3 rows affected (0.00 SEC) Records: 3 Duplicates: 0 Warnings: 0 mysql> insert into `order1`(u_id,username,money)values(1,'zr', 10000), (1,'wld', 13000), (3,'ljc', 25000); Query OK, 3 rows affected (0.00 SEC) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from 'order1'; Query OK, 3 rows affected (0.00 SEC) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from 'order1'; +------+------+----------+-------+ | o_id | u_id | username | money | +------+------+----------+-------+ | 1 | 1 | zr | 10000 | | 2 | 1 | wld | 13000 | | 3 | 3 | ljc | 25000 | +------+------+----------+-------+ 3 rowsin set (0.00 sec)

mysql> select * from `user`;
+----+-------+-----+
| id | name  | sex |
+----+-------+-----+
|  1 | zr    |   2 |
|  2 | wld   |   1 |
|  3 | zmedu |   1 |
+----+-------+-----+
3 rows in set (0.00 sec)


Copy the code
mysql> select id,name,sex,money, o_id from `user`,`order1` where id=u_id;
+----+-------+-----+-------+------+
| id | name  | sex | money | o_id |
+----+-------+-----+-------+------+
|  1 | zr    |   2 | 10000 |    1 |
|  1 | zr    |   2 | 13000 |    2 |
|  3 | zmedu |   1 | 25000 |    3 |
+----+-------+-----+-------+------+
3 rows in set (0.00 sec)

Copy the code

Test cascading delete: we delete user ID1 and see if user id1 in order1 has been deleted

mysql> delete from user whereid =1; Query OK, 1 row affected (0.00 SEC) mysql> select * from 'order1'; +----------+------+----------+-------+ | order_id | u_id | username | money | +----------+------+----------+-------+ | 3  | 3 | ljc | 25000 | +----------+------+----------+-------+ 1 rowin set (0.00 sec)

Copy the code

Testing cascading updates: Data status before update

mysql> select * from `order1`; +----------+------+----------+-------+ | order_id | u_id | username | money | +----------+------+----------+-------+ | 3  | 3 | ljc | 25000 | +----------+------+----------+-------+ 1 rowin set (0.00 sec)

mysql> select * from user;
+----+-------+-----+
| id | name  | sex |
+----+-------+-----+
|  2 | wld   |   1 |
|  3 | zmedu |   1 |
+----+-------+-----+
2 rows in set (0.00 sec)

mysql> update user set id=5 where id=3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from user;
+----+-------+-----+
| id | name  | sex |
+----+-------+-----+
|  2 | wld   |   1 |
|  5 | zmedu |   1 |
+----+-------+-----+
2 rows in set (0.00 sec)

Copy the code

Alter table alter table create foreign key and cascade update

Alter table 表名 add [constraint [constraint [constraint]] foreign key References Data table... [on update cascade|set null|no action]
[on delete cascade|set null|no action]
)
Copy the code

Mysql > ALTER TABLE order1 drop foreign key order1_ibfk_1; alter table order1 drop foreign key order1_ibfk_1; mysql> show create table order1;

Show create table order1; +--------+-------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------ --------------------------------------+ | Table | Create Table | +--------+-------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------ --------------------------------------+ | order1 | CREATE TABLE `order1` ( `order_id` int(11) NOT NULL AUTO_INCREMENT, `u_id` int(11) DEFAULT'0', `username` varchar(40) DEFAULT NULL, `money` int(11) DEFAULT NULL, PRIMARY KEY (`order_id`), KEY `u_id` (`u_id`), CONSTRAINT `order1_ibfk_1` FOREIGN KEY (`u_id`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 | +--------+-------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------ --------------------------------------+ 1 rowin set(0.00 SEC) mysql> alter table order1 drop foreign key order1_ibfk_1; Query OK, 1 row affected (0.02sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> show create table order1; Query OK, 1 row affected (0.02sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> show create table order1; +--------+-------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------ ----------------------------------------------+ | Table | Create Table | +--------+-------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------ ----------------------------------------------+ | order1 | CREATE TABLE `order1` ( `order_id` int(11) NOT NULL AUTO_INCREMENT, `u_id` int(11) DEFAULT'0',
  `username` varchar(40) DEFAULT NULL,
  `money` int(11) DEFAULT NULL,
  PRIMARY KEY (`order_id`),
  KEY `u_id` (`u_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 |

1 row in set (0.00 sec)



Copy the code