A quick tutorial on primary and foreign keys in MySQL

The relationship between the primary key and the foreign key, more colloquially, I now have a forum, there are two tables, one is the main post thread, the other is the reply reply

Let's start with the primary key. The primary key is the only field in the table that recognizes records. The primary key is usually the post id
thread.php? id=1 means I want to access posts where id is 1 ~

Said foreign key again, when we delete a post, you need to perform another 1 operation, is to remove any reply, if under normal circumstances, we need to perform two delete operation (thread and reply), by this time if there is a foreign key, for example, in reply table set up 1 point to thread table primary key (id) the foreign key (the foreign key field, must be a corresponding post id), and specify delete response and that you are deleting thread, mysql will delete all the replies to this post from the reply table itself, without you having to manually perform another delete operation on the reply table

As for the relationship between the two, in our example, the foreign key of the reply table points to the primary key of the thread table ~ ~

Make an example, simple demonstration 1 use, do dage and xiaodi two tables, the elder brother table is the primary key, little brother table is the foreign key:
Build tables:

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 big 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 little brother:

mysql> insert into xiaodi(dage_id,name) values(1,' Causeway bay, _ The younger brother A');

Query OK, 1 row affected (0.02 sec)

mysql> select * from xiaodi;

+----+---------+--------------+
| id | dage_id | name     |
+----+---------+--------------+
| 1 |    1 |  Causeway bay, _ The younger brother A |
+----+---------+--------------+

Delete the eldest brother:

mysql> delete from dage where id=1;

mysql> insert into dage(name) values(' Causeway bay, ');

0

Hint: no way, there are constraints, there are younger brothers under the big brother, can not leave us regardless of ah!

Insert a new younger brother:

mysql> insert into dage(name) values(' Causeway bay, ');

1

mysql> insert into dage(name) values(' Causeway bay, ');

2

Hint: Boy, want to rebel! You don't have a 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; 

mysql> insert into dage(name) values(' Causeway bay, ');

4

mysql> insert into dage(name) values(' Causeway bay, ');

5

mysql> insert into dage(name) values(' Causeway bay, ');

6

Try deleting the older brother again:

mysql> insert into dage(name) values(' Causeway bay, ');

7

Query OK, 1 row affected (0.01 sec)

mysql> insert into dage(name) values(' Causeway bay, ');

9

Empty set (0.01 sec)

mysql> select * from xiaodi;

Empty set (0.00 sec)

Well, this time the corresponding brother also no, no way, who let you with me on delete cascade!

Example should be pretty clear, other functions corresponding to the manual practice it! : -)