揭贴率都比我低啊-|-! 比如创建一个 a表,是innodb类型,字段一个a_idCREATE TABLE a ( a_id INT NOT NULL , PRIMARY KEY ( a_id ) ) TYPE = INNODB; 再创建一个表b,也是innodb类型,字段2个,b_id和 b_a_id 这个 b_a_id就是关联字段了,关联的是a表的a_id字段,显然 b_a_id也可以别的名字,比如a_id,都无所谓 主要是这句话: FOREIGN KEY (b_a_id) REFERENCES a (a_id) ON DELETE CASCADE ) 外键 b_a_id 关联 a表的a_id,当a表记录被删除时,这样,a表记录被删除时,会自动去找b表的,关联字段值相同,也删 除了ON DELETE CASCADE 还有别的,查手册CREATE TABLE b ( b_a_id INT NOT NULL, b_id INT NOT NULL, PRIMARY KEY(b_id), FOREIGN KEY (b_a_id) REFERENCES a (a_id) ON DELETE CASCADE ) TYPE = INNODB;
mysql> CREATE TABLE b -> ( -> b_a_id INT NOT NULL, -> b_id INT NOT NULL, -> PRIMARY KEY(b_id), -> FOREIGN KEY (b_a_id) REFERENCES a -> (a_id) ON DELETE CASCADE -> ) TYPE = INNODB; Query OK, 0 rows affected, 2 warnings (0.06 sec)mysql> insert into a values(1); Query OK, 1 row affected (0.00 sec)mysql> insert into b values(1,1); Query OK, 1 row affected (0.00 sec)mysql> select * from a; +------+ | a_id | +------+ | 1 | +------+ 1 row in set (0.00 sec)mysql> select *from b; +--------+------+ | b_a_id | b_id | +--------+------+ | 1 | 1 | +--------+------+ 1 row in set (0.00 sec)mysql> delete from a where a_id=1; Query OK, 1 row affected (0.00 sec)mysql> select * from a; Empty set (0.00 sec)mysql> select *from b; +--------+------+ | b_a_id | b_id | +--------+------+ | 1 | 1 | +--------+------+ 1 row in set (0.00 sec)
比如创建一个 a表,是innodb类型,字段一个a_idCREATE TABLE a
(
a_id INT NOT NULL ,
PRIMARY KEY ( a_id )
) TYPE = INNODB;
再创建一个表b,也是innodb类型,字段2个,b_id和 b_a_id
这个 b_a_id就是关联字段了,关联的是a表的a_id字段,显然 b_a_id也可以别的名字,比如a_id,都无所谓
主要是这句话:
FOREIGN KEY (b_a_id) REFERENCES a
(a_id) ON DELETE CASCADE
)
外键 b_a_id 关联 a表的a_id,当a表记录被删除时,这样,a表记录被删除时,会自动去找b表的,关联字段值相同,也删
除了ON DELETE CASCADE 还有别的,查手册CREATE TABLE b
(
b_a_id INT NOT NULL,
b_id INT NOT NULL,
PRIMARY KEY(b_id),
FOREIGN KEY (b_a_id) REFERENCES a
(a_id) ON DELETE CASCADE
) TYPE = INNODB;
mysql> CREATE TABLE b
-> (
-> b_a_id INT NOT NULL,
-> b_id INT NOT NULL,
-> PRIMARY KEY(b_id),
-> FOREIGN KEY (b_a_id) REFERENCES a
-> (a_id) ON DELETE CASCADE
-> ) TYPE = INNODB;
Query OK, 0 rows affected, 2 warnings (0.06 sec)mysql> insert into a values(1);
Query OK, 1 row affected (0.00 sec)mysql> insert into b values(1,1);
Query OK, 1 row affected (0.00 sec)mysql> select * from a;
+------+
| a_id |
+------+
| 1 |
+------+
1 row in set (0.00 sec)mysql> select *from b;
+--------+------+
| b_a_id | b_id |
+--------+------+
| 1 | 1 |
+--------+------+
1 row in set (0.00 sec)mysql> delete from a where a_id=1;
Query OK, 1 row affected (0.00 sec)mysql> select * from a;
Empty set (0.00 sec)mysql> select *from b;
+--------+------+
| b_a_id | b_id |
+--------+------+
| 1 | 1 |
+--------+------+
1 row in set (0.00 sec)