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

MySQL: Add constraint if not exists

提问by Svish

In my create script for my database create script looking something like this:

在我的数据库创建脚本的创建脚本中，看起来像这样：

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`);

This runs fine the first time, but if I run it again it fails on the last line there with "Duplicate key on write or update".

这第一次运行良好，但如果我再次运行它，它会在最后一行失败，并显示“写入或更新时复制密钥”。

Is there a way I can do sort of a ADD CONSTRAINT IF NOT EXISTSor something like that? Like I can do with the CREATE TABLEquery?

有没有办法我可以做类似的ADD CONSTRAINT IF NOT EXISTS事情？就像我可以做的CREATE TABLE查询？

采纳答案by Daniel Vassallo

Interesting question. You may want to disable foreign keys before you call your CREATE TABLEstatements and enable them afterwards. This will allow you to define the foreign keys directly in the CREATE TABLEDDL:

有趣的问题。您可能希望在调用CREATE TABLE语句之前禁用外键，然后再启用它们。这将允许您直接在CREATE TABLEDDL 中定义外键：

Example:

例子：

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)

Test case:

测试用例：

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

回答by Paul Ostrowski

The FOREIGN_KEY_CHECKSis a great tools but if your need to know how to do this without dropping and recreating your tables. You can use a SELECTstatement ON information_schema.TABLE_CONSTRAINTSto determine if the foreign key exists:

这FOREIGN_KEY_CHECKS是一个很棒的工具，但是如果您需要知道如何在不删除和重新创建表的情况下执行此操作。您可以使用SELECTON 语句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

回答by seanf

MariaDB supports this syntax in 10.0.2 or later:

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`);