mysql添加约束之前不满足_MySQL：添加约束（如果不存在）

小编典典

有趣的问题。您可能需要在调用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

总结

以上是生活随笔为你收集整理的mysql添加约束之前不满足_MySQL：添加约束（如果不存在）的全部内容，希望文章能够帮你解决所遇到的问题。

如果觉得生活随笔网站内容还不错，欢迎将生活随笔推荐给好友。