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?


mysql constraints
person Svish    schedule 12.10.2010    source источник

Ответы (3)


arrow_upward
21
arrow_downward

Интересный вопрос. Вы можете захотеть отключить внешние ключи, прежде чем вызывать свои операторы CREATE TABLE, а затем включить их. Это позволит вам определить внешние ключи непосредственно в CREATE TABLE DDL:

Пример:

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
person Daniel Vassallo    schedule 12.10.2010
comment
Хм, я думал, что мне нужно создать таблицу A, создать таблицу B с B->A, а затем добавить A->B, потому что ограничение не сработает, если таблицы еще не существует... странно... попробую как можно скорее :п - person Svish; 13.10.2010
comment
@Svish: Было бы, если бы у вас не было SET FOREIGN_KEY_CHECKS = 0; вверху. В этом вся хитрость. Затем мы устанавливаем его обратно на 1 в конце. - person Daniel Vassallo; 13.10.2010
comment
У меня тоже возникла эта проблема, и я попытаюсь обновить свой скрипт, чтобы использовать FOREIGN_KEY_CHECKS. Что произойдет при жесткой ошибке, если я запущу SET FOREIGN_KEY_CHECKS = 1; с недопустимым внешним ключом? - person desto; 28.01.2014
comment
Честно говоря, я мог бы создать процедуру хранения, которая проверяет, существует ли FK из информационной схемы, и добавляет ограничения только в случае необходимости. Затем отбросьте процедуру хранения. - person lcardito; 01.08.2018

arrow_upward
28
arrow_downward

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
person Paul Ostrowski    schedule 28.12.2012
comment
Это очень полезно и оборонительно, мне это нравится - person Rami Jamleh; 01.05.2013
comment
Это решение выглядит так, как будто оно работает, но генерирует следующую ошибку (если вы используете его вместо ALTER TABLE в исходном операторе SQL, опубликованном OP): [ERROR in query 3] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF NOT EXISTS (SELECT NULL FROM information_schema.TABLE_CONSTRAINTS WHERE ' at line 1 [ERROR in query 4] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END IF' at line 1 - person jsdalton; 18.12.2014
comment
@jsdalton Я думаю, что этот синтаксис работает только внутри хранимой процедуры. - person seanf; 25.09.2015

arrow_upward
26
arrow_downward

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`);
person seanf    schedule 25.09.2015
comment
Черт, только мариадб. - person Kzqai; 08.08.2017