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?

score 31 · Answer 1 · edited Nov 21 '18 at 09:51

31

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

edited Nov 21 '18 at 09:51

alexandre-rousseau

2,321
26
33

answered Dec 28 '12 at 16:55

Paul Ostrowski

1,968
16
21

This is very useful and defensive i like it – Rami Jamleh May 01 '13 at 14:36
4

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` – jsdalton Dec 18 '14 at 17:06
@jsdalton I think this syntax only works inside a stored procedure. – seanf Sep 25 '15 at 07:07

score 31 · Answer 2 · answered Sep 25 '15 at 05:04

31

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

answered Sep 25 '15 at 05:04

seanf

6,504
3
42
52

3

Dang, mariadb only. – Kzqai Aug 07 '17 at 22:49
1

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. – Christopher Schultz Aug 21 '22 at 18:48

Daniel Vassallo · Accepted Answer · 2010-10-12T22:09:26.930

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

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 — Svish, Oct 12 '10 at 22:23
@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. — Daniel Vassallo, Oct 12 '10 at 22:25
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? — desto, Jan 28 '14 at 19:33
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. — lcardito, Aug 01 '18 at 12:14

score 1 · Answer 4 · answered Jan 12 '22 at 10:51

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".

MySQL: Add constraint if not exists

4 Answers4

Linked