mysql 外键改成递增_mysql – 如何使用自动递增的主键作为外键？

看起来你反过来引用表和引用表.你可能想做：

ALTER TABLE `child ` ADD FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`);

您还可以在CREATE TABLE语句中定义外键,如下所示：

CREATE TABLE `parent` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`data` text,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

CREATE TABLE `child` (

`parent_id` int(11) DEFAULT NULL,

`related_ids` int(11) DEFAULT NULL,

KEY `parent_id` (`parent_id`),

KEY `related_ids` (`related_ids`),

FOREIGN KEY (`parent_id`) REFERENCES `parent`(`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

测试用例：

INSERT INTO parent (`data`) VALUES ('test data 1');

Query OK, 1 row affected (0.01 sec)

INSERT INTO parent (`data`) VALUES ('test data 2');

Query OK, 1 row affected (0.01 sec)

INSERT INTO child (`parent_id`, `related_ids`) VALUES (1, 100);

Query OK, 1 row affected (0.01 sec)

INSERT INTO child (`parent_id`, `related_ids`) VALUES (2, 100);

Query OK, 1 row affected (0.01 sec)

INSERT INTO child (`parent_id`, `related_ids`) VALUES (3, 100);

ERROR 1452 (23000): Cannot add or update a child row:

a foreign key constraint fails