mysql - Add foreign key if exists -
i want create foreign key 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 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
both of table examples have same name, suposed second table name "pes" mention in constraint examples. 1 should work:
if not exists (select * sys.objects o o.object_id = object_id(n'`rh_pe`.`const`') , 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 "if (not) exists" clausule can find similar question here: if foreign key not exist add foreign key constraint(or drop foreign key constraint if exist) without using name?
Comments
Post a Comment