By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,805 Members | 1,270 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,805 IT Pros & Developers. It's quick & easy.

cascade update / foreign key

P: n/a
Hi!
For the sake of simplicity, I have three tables, Employee, Department and
Work

Employee >---- Department
\ /
\ /
^ ^
Work

The Work table have two columns, empno and depno and consists that the
employee has worked on another department.

Here is my scripts:
create table employee (empno int not null primary key, depno int not null)
create table department (depno int not null primary key)
create table work (empno int not null, depno int not null)

alter table employee add constraint fk_employee_department foreign key
(depno)
references department (depno)
on update cascade

alter table work add constraint fk_work_employee foreign key (empno)
references employee (empno)
on update cascade

alter table work add constraint fk_work_department foreign key (depno)
references department (depno)
on update cascade

My problem is the last command. SQL Server responds:
Server: Msg 1785, Level 16, State 1, Line 1
Introducing FOREIGN KEY constraint 'fk_work_department' on table 'work' may
cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON
UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

But I want the depno in the work table to be updated when a department.depno
changes a value.

Does anyone have a suggestion on how to overcome this problem?

Thanks in advance

Best regards,
Gunnar V°yenli
EDB-konsulent as
NORWAY
Jul 20 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Hi,
SQL statement that you had provided is generating circular refrence in
updates
Workaround is to use triger instead of foreign key integrity.

Comment the one of foriegn key sql and use triger.

Note this trigger are not tested. but is only for your reference and
other work around for problem.

/*alter table work add constraint fk_work_employee foreign key (empno)
references employee (empno)
on update cascade
go
*/
create trigger trgUpdateEmpNoInDept
on employee
for update
as
set nocount on

declare @NewEmpno int , @OldEmpno int

select @NewEmpno = inserted.empno from inserted
select @OldEmpno = deleted.empno from deleted

update work set work.empno = @NewEmpno
where work.empno = @OldEmpno
if @@rowcount=0 or @@error<>0
begin
Rollback tran
end

set nocount off
go

create trigger trgCheckEmpExists
on Work
For Update
as

set nocount on

declare @NewEmpno int
select @NewEmpno = inserted.empno from inserted

If Not Exists (Select * from employee where empno = @NewEmpno )
Begin
RAISERROR ('Empno do not exists', 16, 1)
Rollback Tran
End
set nocount off

go
Thanks & Regards, Amit

"Gunnar V°yenli" <gv@edbkonsulent.no> wrote in message news:<3f********@news.broadpark.no>...
Hi!
For the sake of simplicity, I have three tables, Employee, Department and
Work

Employee >---- Department
\ /
\ /
^ ^
Work

The Work table have two columns, empno and depno and consists that the
employee has worked on another department.

Here is my scripts:
create table employee (empno int not null primary key, depno int not null)
create table department (depno int not null primary key)
create table work (empno int not null, depno int not null)

alter table employee add constraint fk_employee_department foreign key
(depno)
references department (depno)
on update cascade

alter table work add constraint fk_work_employee foreign key (empno)
references employee (empno)
on update cascade

alter table work add constraint fk_work_department foreign key (depno)
references department (depno)
on update cascade

My problem is the last command. SQL Server responds:
Server: Msg 1785, Level 16, State 1, Line 1
Introducing FOREIGN KEY constraint 'fk_work_department' on table 'work' may
cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON
UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

But I want the depno in the work table to be updated when a department.depno
changes a value.

Does anyone have a suggestion on how to overcome this problem?

Thanks in advance

Best regards,
Gunnar V°yenli
EDB-konsulent as
NORWAY

Jul 20 '05 #2

P: n/a
Thank you very much!
-Gunnar

"AMIT" <am***@zenithinfotech.com> wrote in message
news:f3**************************@posting.google.c om...
Hi,
SQL statement that you had provided is generating circular refrence in
updates
Workaround is to use triger instead of foreign key integrity.

Comment the one of foriegn key sql and use triger.

Note this trigger are not tested. but is only for your reference and
other work around for problem.

/*alter table work add constraint fk_work_employee foreign key (empno)
references employee (empno)
on update cascade
go
*/
create trigger trgUpdateEmpNoInDept
on employee
for update
as
set nocount on

declare @NewEmpno int , @OldEmpno int

select @NewEmpno = inserted.empno from inserted
select @OldEmpno = deleted.empno from deleted

update work set work.empno = @NewEmpno
where work.empno = @OldEmpno
if @@rowcount=0 or @@error<>0
begin
Rollback tran
end

set nocount off
go

create trigger trgCheckEmpExists
on Work
For Update
as

set nocount on

declare @NewEmpno int
select @NewEmpno = inserted.empno from inserted

If Not Exists (Select * from employee where empno = @NewEmpno )
Begin
RAISERROR ('Empno do not exists', 16, 1)
Rollback Tran
End
set nocount off

go
Thanks & Regards, Amit

"Gunnar V°yenli" <gv@edbkonsulent.no> wrote in message

news:<3f********@news.broadpark.no>...
Hi!
For the sake of simplicity, I have three tables, Employee, Department and Work

Employee >---- Department
\ /
\ /
^ ^
Work

The Work table have two columns, empno and depno and consists that the
employee has worked on another department.

Here is my scripts:
create table employee (empno int not null primary key, depno int not null) create table department (depno int not null primary key)
create table work (empno int not null, depno int not null)

alter table employee add constraint fk_employee_department foreign key
(depno)
references department (depno)
on update cascade

alter table work add constraint fk_work_employee foreign key (empno)
references employee (empno)
on update cascade

alter table work add constraint fk_work_department foreign key (depno)
references department (depno)
on update cascade

My problem is the last command. SQL Server responds:
Server: Msg 1785, Level 16, State 1, Line 1
Introducing FOREIGN KEY constraint 'fk_work_department' on table 'work' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

But I want the depno in the work table to be updated when a department.depno changes a value.

Does anyone have a suggestion on how to overcome this problem?

Thanks in advance

Best regards,
Gunnar V°yenli
EDB-konsulent as
NORWAY

Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.