Prev: Timeout after Mirroring Failover
Next: Using SSMS2008 to view Job Activity Monitor on SQL2005 - cw
From: Chaplain Doug on 7 Oct 2009 13:41
SQL Server 2005. Windows Server 2003.

I have two tables as follows (this is a small test):

Constituents
ConstitID (Primary Key)
Name

Relationships
ConstitID1
Rel1
ConstitID2
Rel2

I am trying to setup a relationship between the Relationships table and the
Constituents table such that if a record in the Constituents table is
deleted, corresponding records in the Relationships table are deleted
automatically. I want to tie the fields ConstitID1 and ConstitID2 in
Relationships to the ConstitID key in the Constituents table in such a way
that if the record for ConstitID is deleted in the Constituents table any
records in the Relationships table that have the same ConstitID value in
either the ConstitID1 or ConstitID2 fields are deleted.

I tried doing this by establishing foreign key for both the ConstitID1 and
ConstitID2 fields in the Relationships table, relating them to the ConstitID
field in the Constituents Table. I set the "delete rule" to "cascade" for
both foreign keys since this is what I want. SQL did not like that giving me
the error:

'Relationships' table
- Unable to create relationship 'FK_Relationships_Constituents'.
Introducing FOREIGN KEY constraint 'FK_Relationships_Constituents' on table
'Relationships' may cause cycles or multiple cascade paths. Specify ON DELETE
NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

How can I accomplish what I am trying to do? If I delete a record in
Constituents that has value X for ConstitID, then I want to automatically
delete any records in the Relationships table that have the same value X in
either the ConstitID1 or ConstitID2 fields.

Thanks for any help.

--
Dr. Doug Pruiett
Good News Jail & Prison Ministry
www.goodnewsjail.org
From: TheSQLGuru on 7 Oct 2009 14:41
one solution is to have a DELETE trigger on constituents table that will
delete affected records from relationships table.

--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net


"Chaplain Doug" <ChaplainDoug(a)discussions.microsoft.com> wrote in message
news:91B9663B-DCB5-423A-9416-63B8D161DF85(a)microsoft.com...
> SQL Server 2005. Windows Server 2003.
>
> I have two tables as follows (this is a small test):
>
> Constituents
> ConstitID (Primary Key)
> Name
>
> Relationships
> ConstitID1
> Rel1
> ConstitID2
> Rel2
>
> I am trying to setup a relationship between the Relationships table and
> the
> Constituents table such that if a record in the Constituents table is
> deleted, corresponding records in the Relationships table are deleted
> automatically. I want to tie the fields ConstitID1 and ConstitID2 in
> Relationships to the ConstitID key in the Constituents table in such a way
> that if the record for ConstitID is deleted in the Constituents table any
> records in the Relationships table that have the same ConstitID value in
> either the ConstitID1 or ConstitID2 fields are deleted.
>
> I tried doing this by establishing foreign key for both the ConstitID1 and
> ConstitID2 fields in the Relationships table, relating them to the
> ConstitID
> field in the Constituents Table. I set the "delete rule" to "cascade" for
> both foreign keys since this is what I want. SQL did not like that giving
> me
> the error:
>
> 'Relationships' table
> - Unable to create relationship 'FK_Relationships_Constituents'.
> Introducing FOREIGN KEY constraint 'FK_Relationships_Constituents' on
> table
> 'Relationships' may cause cycles or multiple cascade paths. Specify ON
> DELETE
> NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
>
> How can I accomplish what I am trying to do? If I delete a record in
> Constituents that has value X for ConstitID, then I want to automatically
> delete any records in the Relationships table that have the same value X
> in
> either the ConstitID1 or ConstitID2 fields.
>
> Thanks for any help.
>
> --
> Dr. Doug Pruiett
> Good News Jail & Prison Ministry
> www.goodnewsjail.org


From: Chaplain Doug on 7 Oct 2009 14:59
How can I go about establishing a DELETE trigger on the Constituents table?
--
Dr. Doug Pruiett
Good News Jail & Prison Ministry
www.goodnewsjail.org


"TheSQLGuru" wrote:

> one solution is to have a DELETE trigger on constituents table that will
> delete affected records from relationships table.
>
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
>
> "Chaplain Doug" <ChaplainDoug(a)discussions.microsoft.com> wrote in message
> news:91B9663B-DCB5-423A-9416-63B8D161DF85(a)microsoft.com...
> > SQL Server 2005. Windows Server 2003.
> >
> > I have two tables as follows (this is a small test):
> >
> > Constituents
> > ConstitID (Primary Key)
> > Name
> >
> > Relationships
> > ConstitID1
> > Rel1
> > ConstitID2
> > Rel2
> >
> > I am trying to setup a relationship between the Relationships table and
> > the
> > Constituents table such that if a record in the Constituents table is
> > deleted, corresponding records in the Relationships table are deleted
> > automatically. I want to tie the fields ConstitID1 and ConstitID2 in
> > Relationships to the ConstitID key in the Constituents table in such a way
> > that if the record for ConstitID is deleted in the Constituents table any
> > records in the Relationships table that have the same ConstitID value in
> > either the ConstitID1 or ConstitID2 fields are deleted.
> >
> > I tried doing this by establishing foreign key for both the ConstitID1 and
> > ConstitID2 fields in the Relationships table, relating them to the
> > ConstitID
> > field in the Constituents Table. I set the "delete rule" to "cascade" for
> > both foreign keys since this is what I want. SQL did not like that giving
> > me
> > the error:
> >
> > 'Relationships' table
> > - Unable to create relationship 'FK_Relationships_Constituents'.
> > Introducing FOREIGN KEY constraint 'FK_Relationships_Constituents' on
> > table
> > 'Relationships' may cause cycles or multiple cascade paths. Specify ON
> > DELETE
> > NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
> >
> > How can I accomplish what I am trying to do? If I delete a record in
> > Constituents that has value X for ConstitID, then I want to automatically
> > delete any records in the Relationships table that have the same value X
> > in
> > either the ConstitID1 or ConstitID2 fields.
> >
> > Thanks for any help.
> >
> > --
> > Dr. Doug Pruiett
> > Good News Jail & Prison Ministry
> > www.goodnewsjail.org
>
>
>
From: Ray Marron on 7 Oct 2009 18:16
Chaplain Doug wrote:
> How can I go about establishing a DELETE trigger on the Constituents table?

Look up CREATE TRIGGER in Books Online. Come back for more specific
help if you get stuck.

--
Ray Marron
From: TheSQLGuru on 7 Oct 2009 18:25
Yepper - BOL is a WONDERFUL resource!

--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net


"Ray Marron" <null(a)raymarron.com> wrote in message
news:OF3cRv5RKHA.4592(a)TK2MSFTNGP06.phx.gbl...
> Chaplain Doug wrote:
>> How can I go about establishing a DELETE trigger on the Constituents
>> table?
>
> Look up CREATE TRIGGER in Books Online. Come back for more specific help
> if you get stuck.
>
> --
> Ray Marron


 |  Next  |  Last
Pages: 1 2
Prev: Timeout after Mirroring Failover
Next: Using SSMS2008 to view Job Activity Monitor on SQL2005 - cw