-1

I want to create a foreign key from 1 table

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

to second Table

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;

I've tried with

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
John_H_Smith
  • 160
  • 12
  • 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
  • 1
    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

1 Answers1

0

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?

Community
  • 1
  • 1
Jorge Navarro
  • 16
  • 1