MySQL:如果不存在则添加约束

在我的数据库创建脚本中创建脚本,如下所示:

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`)
)
ENGINE=InnoDB;

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;

ALTER TABLE `rabbits`
    ADD CONSTRAINT `fk_rabbits_main_page` FOREIGN KEY (`main_page_id`) REFERENCES `rabbit_pages` (`id`);

这在第一次运行正常,但是如果我再次运行它会在最后一行失败并显示"写入或更新时重复键".

有没有办法可以做某种类似的ADD CONSTRAINT IF NOT EXISTS东西？就像我可以用CREATE TABLE查询做的那样？

1> Paul Ostrows..：

---

这FOREIGN_KEY_CHECKS是一个很棒的工具,但如果你需要知道如何做到这一点而不丢弃和重新创建表.您可以使用SELECT语句ON information_schema.TABLE_CONSTRAINTS来确定外键是否存在:

IF NOT EXISTS (
    SELECT NULL 
    FROM information_schema.TABLE_CONSTRAINTS
    WHERE
        CONSTRAINT_SCHEMA = DATABASE() AND
        CONSTRAINT_NAME   = 'fk_rabbits_main_page' AND
        CONSTRAINT_TYPE   = 'FOREIGN KEY'
)
THEN
    ALTER TABLE `rabbits`
    ADD CONSTRAINT `fk_rabbits_main_page`
    FOREIGN KEY (`main_page_id`)
    REFERENCES `rabbit_pages` (`id`);
END IF

---

此解决方案看起来会起作用,但会生成以下错误(如果您在OP发布的原始SQL语句中使用它代替ALTER TABLE):`[查询3中的错误]您的SQL语法中有错误; 查看与您的MySQL服务器版本对应的手册,以便在第1行的"IF NOT EXISTS(SELECT NULL from information_schema.TABLE_CONSTRAINTS WHERE")附近使用正确的语法[查询4中的错误]您的SQL语法中有错误;请检查手册,对应于您的MySQL服务器版本,以便在第1行'END IF'附近使用正确的语法

2> Daniel Vassa..：

---

有趣的问题.您可能希望在调用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

3> seanf..：

---

MariaDB在10.0.2或更高版本中支持此语法:

ALTER TABLE `rabbits`
ADD CONSTRAINT `fk_rabbits_main_page` FOREIGN KEY IF NOT EXISTS
(`main_page_id`) REFERENCES `rabbit_pages` (`id`);

---

亲爱的，只有mariadb。