CODEWITHSUNDEEP
sql-servert-sql
androidrill
androidrill

Reputation: 99

Getting error on create table with foreign key constraint (Cascade)

create table employee
(
  eid int primary key,
  ename varchar(50),
  cid int,
  sid int,
  constraint fk_hello1 foreign key(cid) references country(cid)on delete 
  cascade on update cascade,         
  constraint fk_hello2 foreign key(sid) references state(sid) on delete
  cascade on update cascade,
)

I have been trying to apply this code but am constantly getting the error msg.........

ERROR MESSAGE
Msg 1785, Level 16, State 0, Line 1
Introducing FOREIGN KEY constraint 'fk_hello2' on table 'employee' may 
cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or 
ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.

Upvotes: 2

Views: 4943

Answers (1)

Aleksei Semidotskii
Aleksei Semidotskii

Reputation: 1455

You can not use on update cascade when you have cycle references in you relations in database structure:

enter image description here

Only NO ACTION allowed (see you error Specify ON DELETE NO ACTION or ON UPDATE NO ACTION)

Is State from country has a different meaning then state in employee ?

Correct solution:

enter image description here

and table [Employee] must have one foreign key on multiple columns - FK(CID, SID)

Upvotes: 3

Related Questions