MySQL: Add constraint if not exists

Svish_39321 0
Viewed 45716

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?

constraints mysql
4 Answers

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

user_35894480
Related