我建了两个表person和shirt
其中shirt有一个owner字段是外键,引用的是person的主键id
表示shirt的所有者是person中的一个row
于是有如下操作:
两个表:
person
+-------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | | PRI | NULL | auto_increment |
| name | char(60) | | | | |
+-------+----------------------+------+-----+---------+----------------+
shirt
+-------+--------------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra
|
+-------+--------------------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | | PRI | NULL | auto_increment |
| style | enum('t-shirt','polo','dress') | | | t-shirt | |
| owner | smallint(5) unsigned | | | 0 |
|
+-------+--------------------------------+------+-----+---------+----------------+
增加外键
ALTER TABLE `sample`.`shirt` ADD CONSTRAINT `owner` FOREIGN KEY `owner` (`id`)
REFERENCES `person` (`id`)
ON DELETE CASCADE
ON UPDATE CASCADE
, COMMENT = '';
增加索引
alter table shirt add index(owner);
以上语句执行都成功,但是插入数据不成功,语句如下:
insert into shirt values(null,'polo',3);
可是person表的id值有3这个值,如下:
mysql> select * from person;
+----+-------+
| id | name |
+----+-------+
| 2 | zhang |
| 3 | liu |
+----+-------+
2 rows in set (0.00 sec)
其中shirt有一个owner字段是外键,引用的是person的主键id
表示shirt的所有者是person中的一个row
于是有如下操作:
两个表:
person
+-------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | | PRI | NULL | auto_increment |
| name | char(60) | | | | |
+-------+----------------------+------+-----+---------+----------------+
shirt
+-------+--------------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra
|
+-------+--------------------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | | PRI | NULL | auto_increment |
| style | enum('t-shirt','polo','dress') | | | t-shirt | |
| owner | smallint(5) unsigned | | | 0 |
|
+-------+--------------------------------+------+-----+---------+----------------+
增加外键
ALTER TABLE `sample`.`shirt` ADD CONSTRAINT `owner` FOREIGN KEY `owner` (`id`)
REFERENCES `person` (`id`)
ON DELETE CASCADE
ON UPDATE CASCADE
, COMMENT = '';
增加索引
alter table shirt add index(owner);
以上语句执行都成功,但是插入数据不成功,语句如下:
insert into shirt values(null,'polo',3);
可是person表的id值有3这个值,如下:
mysql> select * from person;
+----+-------+
| id | name |
+----+-------+
| 2 | zhang |
| 3 | liu |
+----+-------+
2 rows in set (0.00 sec)