小編典典

有趣的問題。您可能需要在調用CREATE TABLE語句之前禁用外鍵，然后再啟用它們。這將允許您直接在CREATE TABLEDDL中定義外鍵：

例：

SET FOREIGN_KEY_CHECKS = 0;

Query OK, 0 rows affected (0.00 sec)

CREATE TABLE IF NOT EXISTS `rabbits` (

`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,

`name` VARCHAR(255) NOT NULL,

`main_page_id` INT UNSIGNED COMMENT 'What page is the main one',

PRIMARY KEY (`id`),

KEY `main_page_id` (`main_page_id`),

CONSTRAINT `fk_rabbits_main_page` FOREIGN KEY (`main_page_id`) REFERENCES `rabbit_pages` (`id`)

) ENGINE=InnoDB;

Query OK, 0 rows affected (0.04 sec)

CREATE TABLE IF NOT EXISTS `rabbit_pages` (

`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,

`rabbit_id` INT UNSIGNED NOT NULL,

`title` VARCHAR(255) NOT NULL,

`content` TEXT NOT NULL,

PRIMARY KEY (`id`),

KEY `rabbit_id` (`rabbit_id`),

CONSTRAINT `fk_rabbits_pages` FOREIGN KEY (`rabbit_id`) REFERENCES `rabbits` (`id`)

) ENGINE=InnoDB;

Query OK, 0 rows affected (0.16 sec)

SET FOREIGN_KEY_CHECKS = 1;

Query OK, 0 rows affected (0.00 sec)

測試用例：

INSERT INTO rabbits (name, main_page_id) VALUES ('bobby', NULL);

Query OK, 1 row affected (0.02 sec)

INSERT INTO rabbit_pages (rabbit_id, title, content) VALUES (1, 'My Main Page', 'Hello');

Query OK, 1 row affected (0.00 sec)

SELECT * FROM rabbits;

+----+-------+--------------+

| id | name | main_page_id |

+----+-------+--------------+

| 1 | bobby | NULL |

+----+-------+--------------+

1 row in set (0.00 sec)

SELECT * FROM rabbit_pages;

+----+-----------+--------------+---------+

| id | rabbit_id | title | content |

+----+-----------+--------------+---------+

| 1 | 1 | My Main Page | Hello |

+----+-----------+--------------+---------+

1 row in set (0.00 sec)

UPDATE rabbits SET main_page_id = 2 WHERE id = 1;

ERROR 1452 (23000): A foreign key constraint fails

UPDATE rabbits SET main_page_id = 1 WHERE id = 1;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

UPDATE rabbit_pages SET rabbit_id = 2 WHERE id = 1;

ERROR 1452 (23000): A foreign key constraint fails

2020-05-17