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

Popular posts from this blog

aws api gateway - SerializationException in posting new Records via Dynamodb Proxy Service in API -

asp.net - Problems sending emails from forum -