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

Question

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

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 查询一样？

Answer 1

MariaDB 在 10.0.2 or later 中支持这种语法:

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