una - tipos de usuarios mysql

MySQL: Agregar restricción si no existe

Question

En mi script de creación para mi base de datos, cree un script que se parezca a esto:

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

Esto funciona bien la primera vez, pero si lo ejecuto de nuevo, falla en la última línea allí con "Duplicar clave en escritura o actualización".

¿Hay alguna manera de hacer una especie de ADD CONSTRAINT IF NOT EXISTS o algo así? ¿Como puedo hacer con la consulta CREATE TABLE ?

Answer 1

Interesante pregunta. Es posible que desee deshabilitar las claves externas antes de llamar a sus CREATE TABLE y habilitarlas después. Esto le permitirá definir las claves foráneas directamente en el DDL CREATE TABLE :

Ejemplo:

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)

Caso de prueba:

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

Answer 2

MariaDB soporta esta sintaxis en 10.0.2 o posterior :

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

Answer 3

FOREIGN_KEY_CHECKS es una gran herramienta, pero si necesita saber cómo hacerlo sin perder y recrear sus tablas. Puede usar una instrucción SELECT ON information_schema.TABLE_CONSTRAINTS para determinar si existe la clave foránea:

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