uninstall SQL: foreign

Showing posts with label foreign. Show all posts

Monday, March 19, 2012

Cascading Referential Integrity Constraints

In a master-detail one-to-many relationship, I have the foreign key set to '
allow null'. I would like, in this particular case, to automatically have th
e foreign key set to null when the master/one record is deleted.
My understanding from the 'books on line' is that cascading a delete will al
ways delete the detail/many records when the master/one record is deleted. I
f the foreign key is nullable, would it not make sense to null it, and if it
is not nullable to delete
the detail/many records?
Is there any efficient way to set a table up so that foreign keys are automa
tically nulled when the primary key record is deleted?That functionality won't be available until the next release of SQL Server
(Yukon). Meanwhile, you will have to handle RI through triggers in that
case. This link may be useful:
http://msdn.microsoft.com/library/d...efintegrity.asp
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"John Austin" <John.Austin@.ManagedNewsgroups.com> wrote in message
news:025F9D33-F01D-43AA-BF12-09C50AAD4670@.microsoft.com...
In a master-detail one-to-many relationship, I have the foreign key set to
'allow null'. I would like, in this particular case, to automatically have
the foreign key set to null when the master/one record is deleted.
My understanding from the 'books on line' is that cascading a delete will
always delete the detail/many records when the master/one record is deleted.
If the foreign key is nullable, would it not make sense to null it, and if
it is not nullable to delete the detail/many records?
Is there any efficient way to set a table up so that foreign keys are
automatically nulled when the primary key record is deleted?

Sunday, March 11, 2012

Cascading deletes - Which is better - A Trigger or Foreign Key cascading delete?

I need to implement my cascading deletes on a SQL database. Is it better (performance/reliablility-wise) to use the Foreign Key Cascading Deletes or to just write my own triggers to do the deletes?
I was hoping someone had experimented and found which works best.DRI(declarative referential integrity) triggers are used when Cascade Deletes and Updates cannot be implemented in the RDBMS(relational database management system) as in SQL Server 7.0 and below. In SQL Server 2000 using Cascade Delete and Cascade Update is the way to go. The reason is reliability related because DRI(declarative referential integrity) with Cascade Delete and Cascade Update is guaranteed.
On a side note if you have a why question run a search for Peter Chen 1976 ERD paper on Google and see the 26 pages paper in Algebra that created DRI(declarative referential integrity) in ANSI SQL. Hope this helps.|||Thanks for the great info. All my servers run SQL 2000 so we'll be going with the built-in cascading deletes.

Thursday, March 8, 2012

Cascade updates

I ran a bulk update on a SQL database for a range between dateswhere there
were trigger and possibly foreign keys. the parent updated fine but it
changed multiple child records although they were not relatedHi
Can you show us DDL + sample data + your query?
"Dangerfield" <Dangerfield@.discussions.microsoft.com> wrote in message
news:2E57BC94-A3B0-46CA-9349-C2DEB7C79F46@.microsoft.com...
> I ran a bulk update on a SQL database for a range between dateswhere there
> were trigger and possibly foreign keys. the parent updated fine but it
> changed multiple child records although they were not related

cascade update / foreign key

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 Vyenli
EDB-konsulent as
NORWAYHi,

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

Thanks & Regards, Amit

"Gunnar Vyenli" <gv@.edbkonsulent.no> wrote in message news:<3f6a24cb$1@.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 Vyenli
> EDB-konsulent as
> NORWAY|||Thank you very much!
-Gunnar

"AMIT" <amitb@.zenithinfotech.com> wrote in message
news:f3093a66.0309182236.2313bc10@.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 Vyenli" <gv@.edbkonsulent.no> wrote in message
news:<3f6a24cb$1@.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 Vyenli
> > EDB-konsulent as
> > NORWAY

Cascade Deletes

I cannot understand why I receive the following error
mesage when trying to Create a cascading delete
constraint.
Introducing FOREIGN KEY
constraint 'FK_DEALATTR_RELATION__DEAL' on
table 'DealAttribute' may cause cycles or multiple
cascade paths. Specify ON DELETE NO ACTION or ON UPDATE
NO ACTION, or modify other FOREIGN KEY constraints.
Deal Table
CREATE TABLE [Deal] (
[DealID] [int] NOT NULL ,
[Generation] [int] NOT NULL ,
[Reference] [char] (20),
[CptyID] [int] NULL
............
............
............
............
CONSTRAINT [PK_DEAL] PRIMARY KEY CLUSTERED
(
[DealID],
[Generation])
**********************************************************
******
Deal Attribute Table
CREATE TABLE [DealAttribute] (
[DealID] [int] NOT NULL ,
[Generation] [int] NOT NULL ,
[AttributeID] [int] NOT NULL ,
............
............
............
CONSTRAINT [PK_DEALATTRIBUTE] PRIMARY KEY CLUSTERED
(
[DealID],
[Generation],
[AttributeID])
I then want to add a constraint on the DealAttribute
table that references DealID and Generation in the Deal
table. I want the constraint to cascade Delete/Update
i.e. when I delete a record from the deal table for the
corresponding record to be removed from the DealAttribute
table. But I get the above error. Below is the SQL I
use to create the Constraint.
if exists (select 1
from sysobjects
where id = object_id
('FK_DEALATTR_RELATION__DEAL')
and type = 'FK')
alter table DealAttribute
drop constraint FK_DEALATTR_RELATION__DEAL
go
alter table DealAttribute
add constraint FK_DEALATTR_RELATION__DEAL foreign key
(DealID, Generation)
references Deal (DealID, Generation)
on update cascade on delete cascade
go
I dont see how it is cyclic.
Please help.
Thanks
JamieJamie
Read the error message which says what is exactly the problem
You have tried to create a constraint that refernces to the table with two
columns(DealID, Generation)
The below script will work for you
CREATE TABLE Parent
(
[ID] INT NOT NULL PRIMARY KEY,
[NAME]CHAR(1) NOT NULL
)
INSERT INTO Parent VALUES (1,'A')
INSERT INTO Parent VALUES (2,'B')
INSERT INTO Parent VALUES (3,'C')
CREATE TABLE Child
(
[ID] INT NOT NULL PRIMARY KEY,
GFID INT NOT NULL FOREIGN KEY REFERENCES Parent([ID])ON DELETE CASCADE ON
UPDATE CASCADE,
[NAME]CHAR(2) NOT NULL
)
INSERT INTO Child VALUES (1,1,'AA')
INSERT INTO Child VALUES (2,1,'AA')
INSERT INTO Child VALUES (3,2,'BB')
INSERT INTO Child VALUES (4,2,'BB')
INSERT INTO Child VALUES (5,2,'BB')
INSERT INTO Child VALUES (6,3,'CC')
"Jamie" <anonymous@.discussions.microsoft.com> wrote in message
news:2670701c4628c$11b4edb0$a401280a@.phx.gbl...
> I cannot understand why I receive the following error
> mesage when trying to Create a cascading delete
> constraint.
> Introducing FOREIGN KEY
> constraint 'FK_DEALATTR_RELATION__DEAL' on
> table 'DealAttribute' may cause cycles or multiple
> cascade paths. Specify ON DELETE NO ACTION or ON UPDATE
> NO ACTION, or modify other FOREIGN KEY constraints.
> Deal Table
> CREATE TABLE [Deal] (
> [DealID] [int] NOT NULL ,
> [Generation] [int] NOT NULL ,
> [Reference] [char] (20),
> [CptyID] [int] NULL
> ............
> ............
> ............
> ............
> CONSTRAINT [PK_DEAL] PRIMARY KEY CLUSTERED
> (
> [DealID],
> [Generation])
> **********************************************************
> ******
> Deal Attribute Table
> CREATE TABLE [DealAttribute] (
> [DealID] [int] NOT NULL ,
> [Generation] [int] NOT NULL ,
> [AttributeID] [int] NOT NULL ,
> ............
> ............
> ............
> CONSTRAINT [PK_DEALATTRIBUTE] PRIMARY KEY CLUSTERED
> (
> [DealID],
> [Generation],
> [AttributeID])
> I then want to add a constraint on the DealAttribute
> table that references DealID and Generation in the Deal
> table. I want the constraint to cascade Delete/Update
> i.e. when I delete a record from the deal table for the
> corresponding record to be removed from the DealAttribute
> table. But I get the above error. Below is the SQL I
> use to create the Constraint.
> if exists (select 1
> from sysobjects
> where id = object_id
> ('FK_DEALATTR_RELATION__DEAL')
> and type = 'FK')
> alter table DealAttribute
> drop constraint FK_DEALATTR_RELATION__DEAL
> go
> alter table DealAttribute
> add constraint FK_DEALATTR_RELATION__DEAL foreign key
> (DealID, Generation)
> references Deal (DealID, Generation)
> on update cascade on delete cascade
> go
> I dont see how it is cyclic.
> Please help.
> Thanks
> Jamie|||Uri,
I reference the two columns because the combination of
the two make a unique key and are the PK for both
tables. Am I missing something really obvious here?
Thanks
Jamie
>--Original Message--
>Jamie
>Read the error message which says what is exactly the
problem
>You have tried to create a constraint that refernces to
the table with two
>columns(DealID, Generation)
>The below script will work for you
>CREATE TABLE Parent
>(
> [ID] INT NOT NULL PRIMARY KEY,
> [NAME]CHAR(1) NOT NULL
>)
>INSERT INTO Parent VALUES (1,'A')
>INSERT INTO Parent VALUES (2,'B')
>INSERT INTO Parent VALUES (3,'C')
>CREATE TABLE Child
>(
> [ID] INT NOT NULL PRIMARY KEY,
> GFID INT NOT NULL FOREIGN KEY REFERENCES Parent([ID])ON
DELETE CASCADE ON
>UPDATE CASCADE,
> [NAME]CHAR(2) NOT NULL
>)
>INSERT INTO Child VALUES (1,1,'AA')
>INSERT INTO Child VALUES (2,1,'AA')
>INSERT INTO Child VALUES (3,2,'BB')
>INSERT INTO Child VALUES (4,2,'BB')
>INSERT INTO Child VALUES (5,2,'BB')
>INSERT INTO Child VALUES (6,3,'CC')
>
>
>
>"Jamie" <anonymous@.discussions.microsoft.com> wrote in
message
>news:2670701c4628c$11b4edb0$a401280a@.phx.gbl...
>> I cannot understand why I receive the following error
>> mesage when trying to Create a cascading delete
>> constraint.
>> Introducing FOREIGN KEY
>> constraint 'FK_DEALATTR_RELATION__DEAL' on
>> table 'DealAttribute' may cause cycles or multiple
>> cascade paths. Specify ON DELETE NO ACTION or ON UPDATE
>> NO ACTION, or modify other FOREIGN KEY constraints.
>> Deal Table
>> CREATE TABLE [Deal] (
>> [DealID] [int] NOT NULL ,
>> [Generation] [int] NOT NULL ,
>> [Reference] [char] (20),
>> [CptyID] [int] NULL
>> ............
>> ............
>> ............
>> ............
>> CONSTRAINT [PK_DEAL] PRIMARY KEY CLUSTERED
>> (
>> [DealID],
>> [Generation])
>>
**********************************************************
>> ******
>> Deal Attribute Table
>> CREATE TABLE [DealAttribute] (
>> [DealID] [int] NOT NULL ,
>> [Generation] [int] NOT NULL ,
>> [AttributeID] [int] NOT NULL ,
>> ............
>> ............
>> ............
>> CONSTRAINT [PK_DEALATTRIBUTE] PRIMARY KEY
CLUSTERED
>> (
>> [DealID],
>> [Generation],
>> [AttributeID])
>> I then want to add a constraint on the DealAttribute
>> table that references DealID and Generation in the Deal
>> table. I want the constraint to cascade Delete/Update
>> i.e. when I delete a record from the deal table for the
>> corresponding record to be removed from the
DealAttribute
>> table. But I get the above error. Below is the SQL I
>> use to create the Constraint.
>> if exists (select 1
>> from sysobjects
>> where id = object_id
>> ('FK_DEALATTR_RELATION__DEAL')
>> and type = 'FK')
>> alter table DealAttribute
>> drop constraint FK_DEALATTR_RELATION__DEAL
>> go
>> alter table DealAttribute
>> add constraint FK_DEALATTR_RELATION__DEAL foreign
key
>> (DealID, Generation)
>> references Deal (DealID, Generation)
>> on update cascade on delete cascade
>> go
>> I dont see how it is cyclic.
>> Please help.
>> Thanks
>> Jamie
>
>.
>|||Jamie
Look at this helps you.
CREATE TABLE Test
(
col1 INT NOT NULL REFERNCES Table (col1),
col2 INT NOT NULL REFERNCES Table1 (col2),
Primary key (col,col2)
)
"Jamie" <anonymous@.discussions.microsoft.com> wrote in message
news:267be01c46290$e4e593c0$a501280a@.phx.gbl...
> Uri,
> I reference the two columns because the combination of
> the two make a unique key and are the PK for both
> tables. Am I missing something really obvious here?
> Thanks
> Jamie
> >--Original Message--
> >Jamie
> >Read the error message which says what is exactly the
> problem
> >
> >You have tried to create a constraint that refernces to
> the table with two
> >columns(DealID, Generation)
> >The below script will work for you
> >
> >CREATE TABLE Parent
> >(
> > [ID] INT NOT NULL PRIMARY KEY,
> > [NAME]CHAR(1) NOT NULL
> >)
> >INSERT INTO Parent VALUES (1,'A')
> >INSERT INTO Parent VALUES (2,'B')
> >INSERT INTO Parent VALUES (3,'C')
> >
> >CREATE TABLE Child
> >(
> > [ID] INT NOT NULL PRIMARY KEY,
> > GFID INT NOT NULL FOREIGN KEY REFERENCES Parent([ID])ON
> DELETE CASCADE ON
> >UPDATE CASCADE,
> > [NAME]CHAR(2) NOT NULL
> >)
> >
> >INSERT INTO Child VALUES (1,1,'AA')
> >INSERT INTO Child VALUES (2,1,'AA')
> >INSERT INTO Child VALUES (3,2,'BB')
> >INSERT INTO Child VALUES (4,2,'BB')
> >INSERT INTO Child VALUES (5,2,'BB')
> >INSERT INTO Child VALUES (6,3,'CC')
> >
> >
> >
> >
> >
> >
> >"Jamie" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:2670701c4628c$11b4edb0$a401280a@.phx.gbl...
> >> I cannot understand why I receive the following error
> >> mesage when trying to Create a cascading delete
> >> constraint.
> >>
> >> Introducing FOREIGN KEY
> >> constraint 'FK_DEALATTR_RELATION__DEAL' on
> >> table 'DealAttribute' may cause cycles or multiple
> >> cascade paths. Specify ON DELETE NO ACTION or ON UPDATE
> >> NO ACTION, or modify other FOREIGN KEY constraints.
> >>
> >> Deal Table
> >>
> >> CREATE TABLE [Deal] (
> >> [DealID] [int] NOT NULL ,
> >> [Generation] [int] NOT NULL ,
> >> [Reference] [char] (20),
> >> [CptyID] [int] NULL
> >> ............
> >> ............
> >> ............
> >> ............
> >>
> >> CONSTRAINT [PK_DEAL] PRIMARY KEY CLUSTERED
> >> (
> >> [DealID],
> >> [Generation])
> >>
> >>
> **********************************************************
> >> ******
> >>
> >> Deal Attribute Table
> >>
> >> CREATE TABLE [DealAttribute] (
> >> [DealID] [int] NOT NULL ,
> >> [Generation] [int] NOT NULL ,
> >> [AttributeID] [int] NOT NULL ,
> >> ............
> >> ............
> >> ............
> >>
> >> CONSTRAINT [PK_DEALATTRIBUTE] PRIMARY KEY
> CLUSTERED
> >> (
> >> [DealID],
> >> [Generation],
> >> [AttributeID])
> >>
> >> I then want to add a constraint on the DealAttribute
> >> table that references DealID and Generation in the Deal
> >> table. I want the constraint to cascade Delete/Update
> >> i.e. when I delete a record from the deal table for the
> >> corresponding record to be removed from the
> DealAttribute
> >> table. But I get the above error. Below is the SQL I
> >> use to create the Constraint.
> >>
> >> if exists (select 1
> >> from sysobjects
> >> where id = object_id
> >> ('FK_DEALATTR_RELATION__DEAL')
> >> and type = 'FK')
> >> alter table DealAttribute
> >> drop constraint FK_DEALATTR_RELATION__DEAL
> >> go
> >>
> >> alter table DealAttribute
> >> add constraint FK_DEALATTR_RELATION__DEAL foreign
> key
> >> (DealID, Generation)
> >> references Deal (DealID, Generation)
> >> on update cascade on delete cascade
> >> go
> >>
> >> I dont see how it is cyclic.
> >>
> >> Please help.
> >> Thanks
> >> Jamie
> >
> >
> >.
> >|||Uri,
This is no good. Because there are multiple DealIDs with
different Generations in the DealAttribute table.
So if you deleted DealID from Deal table all DealID's
would go in DealAttribute table. Regardless of
generation. The two columns are a compund key. Is it
not possible to have a cascade delete with a compound key?
Cheers
Jamie...
>--Original Message--
>Jamie
>Look at this helps you.
>
>CREATE TABLE Test
>(
> col1 INT NOT NULL REFERNCES Table (col1),
> col2 INT NOT NULL REFERNCES Table1 (col2),
> Primary key (col,col2)
>)
>
>"Jamie" <anonymous@.discussions.microsoft.com> wrote in
message
>news:267be01c46290$e4e593c0$a501280a@.phx.gbl...
>> Uri,
>> I reference the two columns because the combination of
>> the two make a unique key and are the PK for both
>> tables. Am I missing something really obvious here?
>> Thanks
>> Jamie
>> >--Original Message--
>> >Jamie
>> >Read the error message which says what is exactly the
>> problem
>> >
>> >You have tried to create a constraint that refernces
to
>> the table with two
>> >columns(DealID, Generation)
>> >The below script will work for you
>> >
>> >CREATE TABLE Parent
>> >(
>> > [ID] INT NOT NULL PRIMARY KEY,
>> > [NAME]CHAR(1) NOT NULL
>> >)
>> >INSERT INTO Parent VALUES (1,'A')
>> >INSERT INTO Parent VALUES (2,'B')
>> >INSERT INTO Parent VALUES (3,'C')
>> >
>> >CREATE TABLE Child
>> >(
>> > [ID] INT NOT NULL PRIMARY KEY,
>> > GFID INT NOT NULL FOREIGN KEY REFERENCES Parent([ID])
ON
>> DELETE CASCADE ON
>> >UPDATE CASCADE,
>> > [NAME]CHAR(2) NOT NULL
>> >)
>> >
>> >INSERT INTO Child VALUES (1,1,'AA')
>> >INSERT INTO Child VALUES (2,1,'AA')
>> >INSERT INTO Child VALUES (3,2,'BB')
>> >INSERT INTO Child VALUES (4,2,'BB')
>> >INSERT INTO Child VALUES (5,2,'BB')
>> >INSERT INTO Child VALUES (6,3,'CC')
>> >
>> >
>> >
>> >
>> >
>> >
>> >"Jamie" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:2670701c4628c$11b4edb0$a401280a@.phx.gbl...
>> >> I cannot understand why I receive the following
error
>> >> mesage when trying to Create a cascading delete
>> >> constraint.
>> >>
>> >> Introducing FOREIGN KEY
>> >> constraint 'FK_DEALATTR_RELATION__DEAL' on
>> >> table 'DealAttribute' may cause cycles or multiple
>> >> cascade paths. Specify ON DELETE NO ACTION or ON
UPDATE
>> >> NO ACTION, or modify other FOREIGN KEY constraints.
>> >>
>> >> Deal Table
>> >>
>> >> CREATE TABLE [Deal] (
>> >> [DealID] [int] NOT NULL ,
>> >> [Generation] [int] NOT NULL ,
>> >> [Reference] [char] (20),
>> >> [CptyID] [int] NULL
>> >> ............
>> >> ............
>> >> ............
>> >> ............
>> >>
>> >> CONSTRAINT [PK_DEAL] PRIMARY KEY CLUSTERED
>> >> (
>> >> [DealID],
>> >> [Generation])
>> >>
>> >>
**********************************************************
>> >> ******
>> >>
>> >> Deal Attribute Table
>> >>
>> >> CREATE TABLE [DealAttribute] (
>> >> [DealID] [int] NOT NULL ,
>> >> [Generation] [int] NOT NULL ,
>> >> [AttributeID] [int] NOT NULL ,
>> >> ............
>> >> ............
>> >> ............
>> >>
>> >> CONSTRAINT [PK_DEALATTRIBUTE] PRIMARY KEY
>> CLUSTERED
>> >> (
>> >> [DealID],
>> >> [Generation],
>> >> [AttributeID])
>> >>
>> >> I then want to add a constraint on the DealAttribute
>> >> table that references DealID and Generation in the
Deal
>> >> table. I want the constraint to cascade
Delete/Update
>> >> i.e. when I delete a record from the deal table for
the
>> >> corresponding record to be removed from the
>> DealAttribute
>> >> table. But I get the above error. Below is the
SQL I
>> >> use to create the Constraint.
>> >>
>> >> if exists (select 1
>> >> from sysobjects
>> >> where id = object_id
>> >> ('FK_DEALATTR_RELATION__DEAL')
>> >> and type = 'FK')
>> >> alter table DealAttribute
>> >> drop constraint FK_DEALATTR_RELATION__DEAL
>> >> go
>> >>
>> >> alter table DealAttribute
>> >> add constraint FK_DEALATTR_RELATION__DEAL foreign
>> key
>> >> (DealID, Generation)
>> >> references Deal (DealID, Generation)
>> >> on update cascade on delete cascade
>> >> go
>> >>
>> >> I dont see how it is cyclic.
>> >>
>> >> Please help.
>> >> Thanks
>> >> Jamie
>> >
>> >
>> >.
>> >
>
>.
>|||Hi,
I started explaining what was wrong with what you were attempting to do, when I realised I was writting utter rubbish. Check to see that you SQL Server is up to date, patch wise.
I ran the following SQL, which is basically yours, and the tables and FK were created without problem. I also poped a couple of rows in the table, and the cascade worked. My SQL Server version is 8.00.818.
Al
CREATE TABLE [Deal] (
[DealID] [int] NOT NULL ,
[Generation] [int] NOT NULL ,
[Reference] [char] (20),
[CptyID] [int] NULL,
CONSTRAINT [PK_DEAL] PRIMARY KEY CLUSTERED
([DealID], [Generation])
)
GO
CREATE TABLE [DealAttribute] (
[DealID] [int] NOT NULL ,
[Generation] [int] NOT NULL ,
[AttributeID] [int] NOT NULL ,
CONSTRAINT [PK_DEALATTRIBUTE] PRIMARY KEY CLUSTERED
([DealID], [Generation], [AttributeID])
)
alter table DealAttribute
add constraint FK_DEALATTR_RELATION__DEAL
foreign key (DealID, Generation)
references Deal (DealID, Generation)
on update cascade on delete cascade
go|||On Mon, 5 Jul 2004 06:40:02 -0700, Jamie wrote:
>Uri,
>This is no good. Because there are multiple DealIDs with
>different Generations in the DealAttribute table.
>So if you deleted DealID from Deal table all DealID's
>would go in DealAttribute table. Regardless of
>generation. The two columns are a compund key. Is it
>not possible to have a cascade delete with a compound key?
>Cheers
>Jamie...
Hi Jamie,
That is possible. There must be another problem.
After reading your post, I had the idea that something was missing. Al's
post confirmed this.
I think that there is already an FK relation with some cascading option
between Deal and DealAttribute. It might even be an indirect relation
(e.g. from Deal to XYZ and from XYZ to DealAttribute). You might want to
check into that.
If you're sure that this is not a case, we need a way to reproduce your
problem. If you can post some CREATE TABLE and ALTER TABLE statements that
will reproduce your problem in an empty database (you can find out for
yourself by creating a play database, running the script in that database,
then dropping the play database again), we can investigate this further.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Cascade Deletes

Cascade Delete Not Working Correctly?

I have a master table that is updated. The master has
child records that are referenced by a foreign key with
the CASCADE DELETE option.
In the transaction log, an update of the master appears
as a DELETE/INSERT. The primary key is not being updated.
For the child record, the same update appears to
DELETE/INSERT the child.
Occassionaly, when a master record is updated, the child
record is deleted but not inserted.
Can anyone explain why?
TIA,
HarryIn order to do this you will need to enable Cascade Update as well as
Cascade Delete...
James Goodman
MCSE MCDBA
http://www.angelfire.com/sports/f1pictures/
"HarryArchibald" <HarryArchibald@.hotmail.com> wrote in message
news:ec2901c4127b$e58cbde0$a001280a@.phx.gbl...
> I have a master table that is updated. The master has
> child records that are referenced by a foreign key with
> the CASCADE DELETE option.
> In the transaction log, an update of the master appears
> as a DELETE/INSERT. The primary key is not being updated.
> For the child record, the same update appears to
> DELETE/INSERT the child.
> Occassionaly, when a master record is updated, the child
> record is deleted but not inserted.
> Can anyone explain why?
> TIA,
> Harry|||My apologies, I've not made myself clear.
I'm not trying to do this.
Firstly, I'm trying to understand why an update of a
master record results in an delete/insert of the child.
Secondly, why the delete part sometimes fails.
TIA.
>--Original Message--
>In order to do this you will need to enable Cascade
Update as well as
>Cascade Delete...
>--
>James Goodman
>MCSE MCDBA
>http://www.angelfire.com/sports/f1pictures/
>"HarryArchibald" <HarryArchibald@.hotmail.com> wrote in
message
>news:ec2901c4127b$e58cbde0$a001280a@.phx.gbl...
updated.
>
>.
>|||What exactly are you auditing to see this?
I cannot replicate this on a sample db I have...
James Goodman
MCSE MCDBA
http://www.angelfire.com/sports/f1pictures/
"HarryArchibald" <HarryArchibald@.hotmail.com> wrote in message
news:13a5701c41284$663d1a40$a101280a@.phx
.gbl...
> My apologies, I've not made myself clear.
> I'm not trying to do this.
> Firstly, I'm trying to understand why an update of a
> master record results in an delete/insert of the child.
> Secondly, why the delete part sometimes fails.
> TIA.
> Update as well as
> message
> updated.|||I'm using the transaction log explorer
tool from Lumigent.
It shows that some updates of the master keep
the child and others do not.
>--Original Message--
>What exactly are you auditing to see this?
>I cannot replicate this on a sample db I have...
>--
>James Goodman
>MCSE MCDBA
>http://www.angelfire.com/sports/f1pictures/
>"HarryArchibald" <HarryArchibald@.hotmail.com> wrote in
message
> news:13a5701c41284$663d1a40$a101280a@.phx
.gbl...
has
with
appears
child
>
>.
>|||"HarryArchibald" <HarryArchibald@.hotmail.com> wrote in message
news:13a5701c41284$663d1a40$a101280a@.phx
.gbl...
> My apologies, I've not made myself clear.
> I'm not trying to do this.
> Firstly, I'm trying to understand why an update of a
> master record results in an delete/insert of the child.
Has the table got triggers associated with it? An Update trigger results in
updates being converted to a delete followed by an insert (so the trigger
can reference the before and after values in the INSERTED and DELETED
tables)
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.614 / Virus Database: 393 - Release Date: 05/03/2004|||The only triggers on the table are SQL Server merge
replication triggers.
Interesting point though. I was not aware of that
behaviour.
>--Original Message--
>"HarryArchibald" <HarryArchibald@.hotmail.com> wrote in
message
> news:13a5701c41284$663d1a40$a101280a@.phx
.gbl...
>Has the table got triggers associated with it? An Update
trigger results in
>updates being converted to a delete followed by an insert
(so the trigger
>can reference the before and after values in the INSERTED
and DELETED
>tables)
>
>--
>Outgoing mail is certified Virus Free.
>Checked by AVG anti-virus system (http://www.grisoft.com).
>Version: 6.0.614 / Virus Database: 393 - Release Date:
05/03/2004
>
>.
>

cascade delete can not apply to two foreign keys

I have a table that contains two foreign keys of two different tables. I want to build a relationship so that when either primary keys deleted in the two tables, the record in the table should be deleted. But, SQL Server does not allow me to save the relationship, it complains that the circling delete might exist. I do not know why, how can I solve this?

Table A:
ID
ProductID <foreign key>
CustomerID <foreign key
Table Product
ProductID <primary key
Table Customer
CustomerID <primary key
I want to cascade delete the record in Table A when either the ProductID is deleted from Product table or the CustomerID is deleted from Customer table.In my opinion, relying on cascading updates and deletes isn't a good thing to do anyway. I think it's better to handle the deletes in your code when you explicitly want them to happen. I.e., the procedures to delete from either the product or customer table should include a delete statement against Table A. I don't think cascading deletes are a good idea to replace your own logic.

CASCADE DELETE (No Action)

Hi,
I have a table that I want to create a Foreign Key constraint on.
This column has NULL values.
I want to create the Foreign Key with a CASCADE DELETE NO ACTION.
I have done this through the script below as I am not sure if this can be do
ne through the GUI in Enterprise Manager. I CAN create a FK through the Ente
rprise Manager GUI for a CASCADE ON DELETE UPDATE and uncheck the check box
"check existing data on cre
ation" and it works fine. Can I do a "ON DELETE NO ACTION" through the GUI a
nd not check existing data on creation?
If not how can I modify my script below to not check the data on creating th
e Foreign Key as this is why I think my Script is not working.
Maybe I should have a Trigger instead?
Any advice/info is much appreciated.
Here is my script I wrote...
ALTER TABLE [dbo].[T_CMT_CONTENT] ADD
CONSTRAINT [FK_T_CMT_CONTENT_T_NWKF_WORKFLOW] FOREIGN KEY
(
[WKF_WORKFLOW_ID]
) REFERENCES [dbo].[T_NWKF_WORKFLOW] (
[WKF_WORKFLOW_ID]
)
ON DELETE NO ACTION
GO
Thanks,
C.On Thu, 27 May 2004 09:21:06 -0700, C wrote:
(snip)
Hi C,
Answered in microsoft.public.sqlserver.programming.
Please don't crosspost!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

CASCADE DELETE (No Action)

Hi,
I have a table that I want to create a Foreign Key constraint on.
This column has NULL values.
I want to create the Foreign Key with a CASCADE DELETE NO ACTION.
I have done this through the script below as I am not sure if this can be done through the GUI in Enterprise Manager. I CAN create a FK through the Enterprise Manager GUI for a CASCADE ON DELETE UPDATE and uncheck the check box "check existing data on cre
ation" and it works fine. Can I do a "ON DELETE NO ACTION" through the GUI and not check existing data on creation?
If not how can I modify my script below to not check the data on creating the Foreign Key as this is why I think my Script is not working.
Maybe I should have a Trigger instead?
Any advice/info is much appreciated.
Here is my script I wrote...
ALTER TABLE [dbo].[T_CMT_CONTENT] ADD
CONSTRAINT [FK_T_CMT_CONTENT_T_NWKF_WORKFLOW] FOREIGN KEY
(
[WKF_WORKFLOW_ID]
) REFERENCES [dbo].[T_NWKF_WORKFLOW] (
[WKF_WORKFLOW_ID]
)
ON DELETE NO ACTION
GO
Thanks,
C.
On Thu, 27 May 2004 09:21:06 -0700, C wrote:
(snip)
Hi C,
Answered in microsoft.public.sqlserver.programming.
Please don't crosspost!
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

CASCADE DELETE (No Action)

Hi
I have a table that I want to create a Foreign Key constraint on
This column has NULL values
I want to create the Foreign Key with a CASCADE DELETE NO ACTION
I have done this through the script below as I am not sure if this can be done through the GUI in Enterprise Manager. I CAN create a FK through the Enterprise Manager GUI for a CASCADE ON DELETE UPDATE and uncheck the check box "check existing data on creation" and it works fine. Can I do a "ON DELETE NO ACTION" through the GUI and not check existing data on creation
If not how can I modify my script below to not check the data on creating the Foreign Key as this is why I think my Script is not working
Maybe I should have a Trigger instead
Any advice/info is much appreciated
Here is my script I wrote...
ALTER TABLE [dbo].[T_CMT_CONTENT] ADD
CONSTRAINT [FK_T_CMT_CONTENT_T_NWKF_WORKFLOW] FOREIGN KEY
[WKF_WORKFLOW_ID
) REFERENCES [dbo].[T_NWKF_WORKFLOW]
[WKF_WORKFLOW_ID
ON DELETE NO ACTION
G
Thanks
COn Thu, 27 May 2004 09:21:06 -0700, C wrote:
(snip)
Hi C,
Answered in microsoft.public.sqlserver.programming.
Please don't crosspost!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Monday, March 19, 2012

Cascading Referential Integrity Constraints

Sunday, March 11, 2012

Cascading deletes - Which is better - A Trigger or Foreign Key cascading delete?

Thursday, March 8, 2012

Cascade updates

cascade update / foreign key

Cascade Deletes

Cascade Deletes

Cascade Deletes

Cascade Delete Not Working Correctly?

cascade delete can not apply to two foreign keys

CASCADE DELETE (No Action)

CASCADE DELETE (No Action)

CASCADE DELETE (No Action)

uninstall SQL

Blog Archive

About Me