Quick understanding of examples of primary key and foreign key in MySQL _ MySQL

This article mainly introduces the difference and connection between the primary key and the foreign key in MySQL. it is the basic knowledge in MySQL beginners. if you need it, you can refer to the relationship between the primary key and the foreign key, in other words, I have a forum with two tables, one with the primary thread and the other with the reply.

First, let's talk about the primary key. the primary key is the only field in the table that identifies records. it is generally the Post id, which is reflected in access, for example
Thread. php? Id = 1 indicates that I want to access a post whose id is 1 ~

Let's talk about the foreign key. when we delete a post, we need to perform another operation, that is, to delete all replies. normally, we need to perform two delete operations (thread and reply). In this case, if a foreign key exists, for example, create a primary key (id) pointing to the thread table in the reply table) (The Field bound to this foreign key must be the id of the corresponding post), and specify the response to delete. when you delete the thread, mysql will help you delete all replies from this post in the reply table, instead of manually executing the delete operation in the reply table ~

In the preceding example, the foreign key of the reply table points to the primary key of the thread table ~~

Let's take an example to illustrate how to use dage and xiaodi. the eldest brother table is the primary key and the younger brother table is the foreign key:
Table creation:

CREATE TABLE `dage` ( `id` int(11) NOT NULL auto_increment, `name` varchar(32) default '', PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=latin1；CREATE TABLE `xiaodi` ( `id` int(11) NOT NULL auto_increment, `dage_id` int(11) default NULL, `name` varchar(32) default '', PRIMARY KEY (`id`), KEY `dage_id` (`dage_id`), CONSTRAINT `xiaodi_ibfk_1` FOREIGN KEY (`dage_id`) REFERENCES `dage` (`id`)) ENGINE=InnoDB DEFAULT CHARSET=latin1；

Insert a eldest brother:

Mysql> insert into dage (name) values ('Causeway Bay ');

Query OK, 1 row affected (0.01 sec)

mysql> select * from dage;

+ ---- + -------- + | Id | name | + ---- + -------- + | 1 | Causeway Bay | + ---- + -------- + 1 row in set (0.00 sec)

Insert a younger brother:

Mysql> insert into xiaodi (dage_id, name) values (1, 'Causeway Bay _ younger brother ');

Query OK, 1 row affected (0.02 sec)

mysql> select * from xiaodi;

+ ---- + --------- + -------------- + | Id | dage_id | name | + ---- + --------- + -------------- + | 1 | 1 | Causeway Bay _ younger brother A | + ---- + --------- + ---------------- +

Delete the eldest brother:

mysql> delete from dage where id=1;

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`bstar/xiaodi`, CONSTRAINT `xiaodi_ibfk_1` FOREIGN KEY (`dage_id`) REFERENCES `dage` (`id`))

Tip: No, there are constraints. you can leave us alone if you have a younger brother or younger brother!

Insert a new younger brother:

Mysql> insert into xiaodi (dage_id, name) values (2, 'mong Kok _ ');

2ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`bstar/xiaodi`, CONSTRAINT `xiaodi_ibfk_1` FOREIGN KEY (`dage_id`) REFERENCES `dage` (`id`))

Tip: I want to fight! You have no big brother yet!

Add the event trigger limit to the foreign key constraint:

mysql> show create table xiaodi;
CONSTRAINT `xiaodi_ibfk_1` FOREIGN KEY (`dage_id`) REFERENCES `dage` (`id`)
mysql> alter table xiaodi drop foreign key xiaodi_ibfk_1; 

Query OK, 1 row affected (0.04 sec)Records: 1 Duplicates: 0 Warnings: 

mysql> alter table xiaodi add foreign key(dage_id) references dage(id) on delete cascade on update cascade;

Query OK, 1 row affected (0.04 sec)Records: 1 Duplicates: 0 Warnings: 0

Try again to delete the eldest brother:

mysql> delete from dage where id=1;

Query OK, 1 row affected (0.01 sec)

mysql> select * from dage;

Empty set (0.01 sec)

mysql> select * from xiaodi;

Empty set (0.00 sec)

Yes, this time the corresponding younger brother is gone. No way. who asked you to talk to me on delete cascade!

The example should be quite clear. practice other functions in the corresponding manual! :-)