Add foreign key constraint if it is not existing

Question

I want to create a foreign key from 1 table, but only if it does not exist.

Tables are created like that:

CREATE TABLE 
IF NOT EXISTS PEs (
    id INT(20) AUTO_INCREMENT PRIMARY KEY, 
    Name varchar(20), 
    Message varchar(30), 
    CoordsX double(9,6) SIGNED,
    CoordsY double(9,6) SIGNED,
    CoordsZ double(9,6) SIGNED,
    Status smallint(1) DEFAULT 1,
    world varchar(20)
) ENGINE = InnoDB;

CREATE TABLE 
IF NOT EXISTS`rh_pe`.`attributes` (
    `toid` INT(20) NOT NULL, 
    `Kommentar` VARCHAR(60) NOT NULL, 
    `Aktion` varchar(10) NOT NULL, 
    `Person1` INT NOT NULL,
    `Person2` INT
) ENGINE = InnoDB;

The Foreign key should be like so:

ALTER TABLE `attributes` 
ADD CONSTRAINT `Const`
FOREIGN KEY (`toid`) REFERENCES `pes`(`id`)
ON DELETE RESTRICT
ON UPDATE RESTRICT;

To create the foreign key, I tried the following two options:

IF NOT EXISTS(
    ALTER TABLE `attributes` 
    ADD CONSTRAINT `Const` 
    FOREIGN KEY (`toid`) REFERENCES `pes`(`id`) 
    ON DELETE RESTRICT 
    ON UPDATE RESTRICT
);

and

ALTER TABLE `attributes` 
ADD  CONSTRAINT `Const` 
FOREIGN KEY 
IF NOT EXISTS (`toid`) REFERENCES `pes`(`id`) 
ON DELETE RESTRICT 
ON UPDATE RESTRICT

But none of them work.

Any Ideas on how I could create the constraint only if it does not exist?

I don't think there's a way to do this in ordinary SQL. You'll need to use a stored procedure that queries `INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS`. See http://dev.mysql.com/doc/refman/5.7/en/referential-constraints-table.html — Barmar, Nov 10 '16 at 17:08
Possible duplicate of [MySQL: Add constraint if not exists](http://stackoverflow.com/questions/3919226/mysql-add-constraint-if-not-exists) — Matteo Tassinari, Nov 10 '16 at 17:25
@MatteoTassinari Okay, in my try before I've got a spelling error. Thanks. — John_H_Smith, Nov 10 '16 at 18:24

score 0 · Accepted Answer · edited May 23 '17 at 12:07

Both of your table examples have the same name, so I suposed that your second table name is "pes" as you mention in your constraint examples. This one should work:

IF NOT EXISTS (SELECT * FROM sys.objects o WHERE o.object_id = object_id(N'`rh_pe`.`Const`') AND OBJECTPROPERTY(o.object_id, N'IsForeignKey') = 1)
BEGIN
    ALTER TABLE `rh_pe`.`attributes` ADD  CONSTRAINT `Const` FOREIGN KEY (`toid`) REFERENCES `rh_pe`.`pes`(`id`) ON DELETE RESTRICT ON UPDATE RESTRICT;
END

I haven't used the "if (not) exists" clausule for this but you can find a similar question here: If Foreign Key Not Exist Then Add Foreign Key Constraint(Or Drop a Foreign Key Constraint If Exist) without using Name?

Sorry, I've used the wrong code of the first table. It is edited. — John_H_Smith, Nov 10 '16 at 18:00

Add foreign key constraint if it is not existing

1 Answers1