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?

Rabiah

Answer 1

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

Huddle

Hm, I thought I had to create table A, create table B with B->A and then add A->B because the constraint would fail if a table does not exist yet... weird... will try this out ASAP :p – Rabiah

@Svish: It would, unless you have that SET FOREIGN_KEY_CHECKS = 0; at the top. That's the trick. We're then setting it back to 1 at the end. – Huddle

I'm having this problem too, and I will try to update my script to use FOREIGN_KEY_CHECKS. What would happen on an error tough, if I run SET FOREIGN_KEY_CHECKS = 1; with an invalid foreign key? – Sales

In all honesty I might create a store procedure that checks if the FK exists from the information schema and adds the constraints only if necessary. Then drop the store procedure. – Outfight

Answer 2

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

Phantasm

Dang, mariadb only. – Gabriel

I've found that this doesn't work for me on MariaDB 10.3. There is no complaint about the syntax, but re-running the same ALTER statement still results in a failure with the error message mentioned in the question. In my case, there were other alterations to the table at the same time as the ADD CONSTRAINT ... FOREIGN KEY so that may have been the problem. – Consolidation

Answer 3

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

Britneybritni

This is very useful and defensive i like it – Nonsectarian

This solution looks like it would work, but generates the following error (if you use it in place of ALTER TABLE in the original SQL statement posted by 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 – Sheena

@Sheena I think this syntax only works inside a stored procedure. – Phantasm

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://mcmap.net/q/210662/-mysql-alter-table-if-column-not-exists

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

Franzoni