MySQL: Add constraint if not exists

Question

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 EXISTS or something like that? Like I can do with the CREATE TABLE query?

Answer 1

MariaDB supports this syntax in 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`);

Answer 2

The FOREIGN_KEY_CHECKS is a great tools but if your need to know how to do this without dropping and recreating your tables. You can use a SELECT statement ON information_schema.TABLE_CONSTRAINTS to determine if the foreign key exists:

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

Answer 3

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

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

Answer 4

For a non-MariaDB, this worked for me:

SET @dbname = DATABASE();
SET @tablename = "my_table";
SET @constraintname = "my_constraint_name";
SET @columnname = "my_column";
SET @othertablename = "other_table";
SET @othercolumnname = "other_column_name";
SET @deleteaction = "CASCADE";
SET @updateaction = "RESTRICT";
SET @preparedStatement = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
    WHERE
      (table_name = @tablename)
      AND (table_schema = @dbname)
      AND (constraint_name = @constraintname)
  ) > 0,
  "SELECT 1",
  CONCAT("ALTER TABLE ",@tablename,
    " ADD CONSTRAINT ",@constraintname,
    " FOREIGN KEY(",@columnname,")
      REFERENCES ",@othertablename,"(",@othercolumnname,")
      ON DELETE ",@deleteaction,
    " ON UPDATE ",@updateaction)));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

This is a solution, is made from a similar problem: https://stackoverflow.com/a/31989541/3589448

Add parameters as needed. @deleteaction and @updateaction can have: "RESTRICT", "CASCADE", "SET NULL" or "NO ACTION".