Multiple Foreign Keys

I am having trouble creating multiple foreign keys on a table so that I can set up cascading update and cascading delete from two different primary tables. I am using the diagram to do this but when I try to save the diagram I get the following error.

ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint 'FK_IndResults_RaceData'. The conflict occurred in database 'VIRA', table 'RaceData', column 'RaceID'.

What would cause this to happen? Is it possible that I have records in the foreign table that do not transfer back to the primary table?

Thanks!Is it possible that I have records in the foreign table that do not transfer back to the primary table?Not just possible, nearly guaranteed.

The best answer is to create a query that will show you the offending rows, then go and fix them. You can often pick a value that makes a reasonable default (sometimes NULL works well, meaning there isn't any relationship). Sometimes you've got to figure out what FK value you need, which can be a lot of work.

-PatP|||Thanks! That was exactly what it was and it is fixed!

Multiple foreign keys

Hi,
I wonder if it is possible to have multiple foreign keys between to tables
and delete data automatically. We have on table with a number of locations
and another table with a number of routes between them. The foreign keys go
from location to the beginning and the end of the route. How can I secure
that (1) only data from location is used in route and (2) if a dataset in
location is deleted both routes to and from are deleted. I can't have a
cascading key on both relations and i couldn't make a trigger work either.
Any suggestions?
Holgerjust a try...
parent table A
child Table B
grandchild table C :)
you have C refering to B refering to A
Create an ondelete cascade to table A
, Now write an intead of delete trigger for table B and table C each which
will delete from table A the corresponding row..
Let me know how it goes.. I am interested in finding out too :)|||Why wouldn't it be possible for both the 'from' and 'to' columns in the rout
e
table to reference the location column in the location table, with ON DELETE
CASCADE?
"HE" wrote:

> Hi,
> I wonder if it is possible to have multiple foreign keys between to tables
> and delete data automatically. We have on table with a number of locations
> and another table with a number of routes between them. The foreign keys g
o
> from location to the beginning and the end of the route. How can I secure
> that (1) only data from location is used in route and (2) if a dataset in
> location is deleted both routes to and from are deleted. I can't have a
> cascading key on both relations and i couldn't make a trigger work either.
> Any suggestions?
> Holger
>
>|||Why wouldn't it be possible for both the 'from' and 'to' columns in the rout
e
table to reference the location column in the location table, with ON DELETE
CASCADE?
"HE" wrote:

> Hi,
> I wonder if it is possible to have multiple foreign keys between to tables
> and delete data automatically. We have on table with a number of locations
> and another table with a number of routes between them. The foreign keys g
o
> from location to the beginning and the end of the route. How can I secure
> that (1) only data from location is used in route and (2) if a dataset in
> location is deleted both routes to and from are deleted. I can't have a
> cascading key on both relations and i couldn't make a trigger work either.
> Any suggestions?
> Holger
>
>|||I think the OP has something like this...
The second on delete cascade, on update cascade causes an error.
Maybe I am missing something in RD Theory, but why would this not be
allowed?
Create table Location
(
LocationID varchar(10) primary key not null
,LocationName varchar(30) not null
)
go
create Table Routes
(
FromLocationID varchar(10) not null
,ToLocationID varchar(10) not null
, RouteName varchar(30) not null
)
go
ALTER TABLE dbo.Routes ADD CONSTRAINT
PK_Routes PRIMARY KEY CLUSTERED
(
FromLocationID,
ToLocationID
)
GO
ALTER TABLE dbo.Routes ADD CONSTRAINT
FK_Routes_FromLocation FOREIGN KEY
(
FromLocationID
) REFERENCES dbo.Location
(
LocationID
) ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE dbo.Routes ADD CONSTRAINT
FK_Routes_ToLocation FOREIGN KEY
(
ToLocationID
) REFERENCES dbo.Location
(
LocationID
) ON UPDATE CASCADE
ON DELETE CASCADE
GO
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:80BE38F5-A266-4593-A4C5-A69DAD837B89@.microsoft.com...
> just a try...
> parent table A
> child Table B
> grandchild table C :)
> you have C refering to B refering to A
> Create an ondelete cascade to table A
> , Now write an intead of delete trigger for table B and table C each
which
> will delete from table A the corresponding row..
> Let me know how it goes.. I am interested in finding out too :)
>|||Hi Jim,
Why do I have a feeling we both are beating the wrong bush (No pun
intended :)
Holger: Of course if its not too much of a pain for you, Can you give the
table definitions and the foriegn key contraints
with a few sample data and your delete statement and what should it inturn
delete and then we will try to find a way out.|||I was thinking along the same lines; I don't think there's anything that
would prevent two columns in one table referencing the same column in anothe
r
table, with both constraints created with ON DELETE CASCADE.
"Jim Underwood" wrote:

> I think the OP has something like this...
> The second on delete cascade, on update cascade causes an error.
> Maybe I am missing something in RD Theory, but why would this not be
> allowed?
> Create table Location
> (
> LocationID varchar(10) primary key not null
> ,LocationName varchar(30) not null
> )
> go
> create Table Routes
> (
> FromLocationID varchar(10) not null
> ,ToLocationID varchar(10) not null
> , RouteName varchar(30) not null
> )
> go
> ALTER TABLE dbo.Routes ADD CONSTRAINT
> PK_Routes PRIMARY KEY CLUSTERED
> (
> FromLocationID,
> ToLocationID
> )
> GO
> ALTER TABLE dbo.Routes ADD CONSTRAINT
> FK_Routes_FromLocation FOREIGN KEY
> (
> FromLocationID
> ) REFERENCES dbo.Location
> (
> LocationID
> ) ON UPDATE CASCADE
> ON DELETE CASCADE
> GO
> ALTER TABLE dbo.Routes ADD CONSTRAINT
> FK_Routes_ToLocation FOREIGN KEY
> (
> ToLocationID
> ) REFERENCES dbo.Location
> (
> LocationID
> ) ON UPDATE CASCADE
> ON DELETE CASCADE
> GO
>
> "Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
> news:80BE38F5-A266-4593-A4C5-A69DAD837B89@.microsoft.com...
> which
>
>|||Logically, I don't see an issue, but I may be missing something. I do know
that SQL Server will not allow this...
From BOL "ON UPDATE CASCADE"
The series of cascading referential actions triggered by a single DELETE or
UPDATE must form a tree containing no circular references. No table can
appear more than once in the list of all cascading referential actions that
result from the DELETE or UPDATE.
******
The tree of cascading referential actions must not have more than one path
to any given table.
******
Any branch of the tree is terminated when it encounters a table for which NO
ACTION has been specified or is the default.
The problem here is we have two paths to the same table, so one would expect
two deletes to be performed, one for each reference, or one delete where
either reference exists. I'm not sure if this is a DBMS thing or a SQL
Server implementation thing. I know that this must be a common issue. I
figure a trigger could accomplish the same thing (which I think omni was
suggesting) but it seems to go against DB design to resort to a trigger when
a simple constraint should be sufficent.
"Mark Williams" <MarkWilliams@.discussions.microsoft.com> wrote in message
news:EBA9B0E2-CA08-4277-8397-3298494C6F15@.microsoft.com...
> I was thinking along the same lines; I don't think there's anything that
> would prevent two columns in one table referencing the same column in
another
> table, with both constraints created with ON DELETE CASCADE.
>
> "Jim Underwood" wrote:
>|||Technically, this is okay in Standard SQL because of the constraints
that force one and only one execution path.
CREATE TABLE Locations
(location_id INTEGER NOT NULL PRIMARY KEY,
location_name VARCHAR(30) NOT NULL);
CREATE TABLE Routes
(route_name VARCHAR(30) NOT NULL PRIMARY KEY,
start_location_id INTEGER NOT NULL
CONSTRAINT starting
REFERENCES Locations (location_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
final_location_id INTEGER NOT NULL
CONSTRAINT ending
REFERENCES Locations (location_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
UNIQUE(start_location_id, final_location_id),
CHECK (start_location_id <> final_location_id));
A smart SQL engine will detect that {ending, starting} and {starting,
ending} will give the same results. This would be legal and deleting a
location removing a node from a graph -- the edges would also
disappear.|||Hi Jim,
"Jim Underwood" <james.underwoodATfallonclinic.com> schrieb im Newsbeitrag
news:Oj6rIiReGHA.3996@.TK2MSFTNGP04.phx.gbl...
> Logically, I don't see an issue, but I may be missing something. I do
> know
> that SQL Server will not allow this...
> From BOL "ON UPDATE CASCADE"
> The series of cascading referential actions triggered by a single DELETE
> or
> UPDATE must form a tree containing no circular references. No table can
> appear more than once in the list of all cascading referential actions
> that
> result from the DELETE or UPDATE.
> ******
> The tree of cascading referential actions must not have more than one path
> to any given table.
> ******
> Any branch of the tree is terminated when it encounters a table for which
> NO
> ACTION has been specified or is the default.
>
> The problem here is we have two paths to the same table, so one would
> expect
> two deletes to be performed, one for each reference, or one delete where
> either reference exists. I'm not sure if this is a DBMS thing or a SQL
> Server implementation thing. I know that this must be a common issue. I
> figure a trigger could accomplish the same thing (which I think omni was
> suggesting) but it seems to go against DB design to resort to a trigger
> when
> a simple constraint should be sufficent.
>
That's exactly my problem. The database looks like those tables you
described earlier and was ported from Oracle where it was no problems having
two cascading actions. I tried using an ON DELETE CASCADE on FromLocation ID
and additionally an AFTER DELETE trigger on Location but that would work
first after the deletion was successful. The deletion can't be succesful as
long as there are entries in Route referencing ToLocationID.
Holger
> "Mark Williams" <MarkWilliams@.discussions.microsoft.com> wrote in message
> news:EBA9B0E2-CA08-4277-8397-3298494C6F15@.microsoft.com...
> another

Multiple fields in where statement

In need to write a delete that checks to see if a record exists in which 3 specific fields match the same 3 fields in another table. If there is a match it deletes that record. Below was my first attempt, which of course doesn't work.

Delete from oop_test where acct_no, curr_seq, acct_type in (select acct_no, curr_seq, acct_type from oop_temp)What about this?

Delete from oop_test
where exists(select 'ok' from oop_temp i where i.acct_no=oop_test.acct_no and ...)|||YES! THAT IS IT! Thanks a TON! I am still a bit confused about the use of the variable "i" in the query. Explain it's use to me. Maybe it is my lack of SQL skills.|||This can also be done with a simple join:

Delete oop_test
from oop_test
inner join oop_temp
on oop_test.acct_no = oop_test.acct_no
and oop_test.curr_seq = oop_test.curr_seq
and oop_test.acct_type = oop_test.acct_type

blindman|||I will have to give this a shot. While the first method works like a champ, I had a difficult time figuring it out. This is real straight forward.

THANKS!|||The "i" in Snail's code is just an alias for the table name oop_temp. Many coders assign shorter alias name for tables in their queries to reduce the amount of typing required. Otherwise, Snail's code just checks each record in oop_test to see is a matching record "exists" in the oop_temp based on the three columns specified.

Both methods work. I think the join method might be more efficient, though for small-to-midsize tables probably not enough to be noticable. Go with whichever method you find easiest to read.

blindman|||Actually, the part I had a problem with was the select 'ok' part. Do you know what that means?|||All right, the 'OK" part is odd! Snail, what was your reasoning for the hard-coded text?

How it works is like this...

The inner query just needs to see whether a corresponding record exists. It doesn't really need to know what any of the record's values are. Therefore, Snail supplied a hard-coded string 'Ok', which will be returned for every matching record. If 'Ok' exists in the recordset, then there was a matching record. Using:

where exists(select * from oop_temp...

...accomplishes the same thing. The optimizer is smart enough to know that you only want to check for the existence of the record, and won't try to access all the columns in the record.

blindman|||Originally posted by blindman
All right, the 'OK" part is odd! Snail, what was your reasoning for the hard-coded text?

How it works is like this...

The inner query just needs to see whether a corresponding record exists. It doesn't really need to know what any of the record's values are. Therefore, Snail supplied a hard-coded string 'Ok', which will be returned for every matching record. If 'Ok' exists in the recordset, then there was a matching record. Using:

where exists(select * from oop_temp...

...accomplishes the same thing. The optimizer is smart enough to know that you only want to check for the existence of the record, and won't try to access all the columns in the record.

blindman

I agree - there is no difference in performance between select 'OK' and select * inside exists, but 'OK' looks much better for me ;)|||So SSchuler, its just a matter of style, and lookin' good! :cool:

blindman|||HEHEH! Good one. Ya learn something new everyday. I can't believe that tripped me up like it did. Now that you point it out, it is quite obvious. Don't let anyone say that us computer jockeys don't have style! ;-)|||* also has to be expanded by the optimizer into the field list, while 'ok' doesn't. i usually use if exists (select 1 from table_name)|||Originally posted by ms_sql_dba
* also has to be expanded by the optimizer into the field list, while 'ok' doesn't. i usually use if exists (select 1 from table_name)

I believe that's changed...Where SELECT * is actually optimized to perform better.|||Brett is correct. Select * is optimized, and the columns are not expanded.

blindman|||Originally posted by SSchuler
In need to write a delete that checks to see if a record exists in which 3 specific fields match the same 3 fields in another table. If there is a match it deletes that record.

To find "a record in which three specific fields match the same three fields in another table," you would use a JOIN clause in a SELECT query.

To delete those fields, you use this select query as a sub-select in a DELETE query, something like this:

DELETE FROM victim WHERE victim_id IN
(SELECT id FROM
table1 A JOIN table2 B USING
A.F1 = B.F1 AND A.F2 = B.F2 AND A.F3 = B.F3
)

(Sub-select italicized for emphasis. Caution: extemporaneous SQL coding... do not try this at home.) ;-)|||A little late, sundial. Read other member's posts first!

blindman

Multiple Deletions From Different Tables in SQL Server Trigger

I have written a trigger that's supposed to go out and delete
corresponding records from multiple tables once I delete a specific
record from a table called tblAdmissions.

This does not work and I'm not sure why...

Here's the code that's supposed to run, let's say, if a user (via a VB
6.0 interface) decides to delete a record. If the record in the
tblAdmissions table has the primary key (AdmissionID) of "123", then
the code below is supposed to search other tables that have related
information in them and also have an AdmissionID of "123" and delete
that information as well.

Any ideas? Here's the code:

CREATE TRIGGER tr_DeleteAdmissionRelatedInfo
-- and here is the table name
ON tblAdmissions
-- the operation type goes here
FOR DELETE

AS
-- I just need one variable this time
DECLARE @.AdmissionID int
-- Now I'll make use of the deleted virtual table
SELECT @.AdmissionID = (SELECT @.AdmissionID FROM Deleted)

-- And now I'll use that value to delete the data in
-- the tblASIFollowUp Table
DELETE FROM tblASIFollowUp
WHERE AdmissionID = @.AdmissionID

-- And now I'll use that value to delete the data in
-- the tblProgramDischarge Table
DELETE FROM tblProgramDischarge
WHERE AdmissionID = @.AdmissionID

-- And now I'll use that value to delete the data in
-- the tblRoomAssignment Table
DELETE FROM tblRoomAssignment
WHERE AdmissionID = @.AdmissionID

-- And now I'll use that value to delete the data in
-- the tblTOADS Table
DELETE FROM tblTOADS
WHERE AdmissionID = @.AdmissionID

-- And now I'll use that value to delete the data in
-- the tblUnitedWaySurvey Table
DELETE FROM tblUnitedWaySurvey
WHERE AdmissionID = @.AdmissionID

-- And now I'll use that value to delete the data in
-- the tblWFGMSurvey Table
DELETE FROM tblWFGMSurvey
WHERE AdmissionID = @.AdmissionIDDoes it all not work or if you break it down into sections does it
still not work. Also how does it handle null values. When I started
using triggers comparisons with Nulls were a right pain. I take it that
all these tables are all in the same database with the same
permissions.

Ginters

bmccollum wrote:
> I have written a trigger that's supposed to go out and delete
> corresponding records from multiple tables once I delete a specific
> record from a table called tblAdmissions.
> This does not work and I'm not sure why...
> Here's the code that's supposed to run, let's say, if a user (via a
VB
> 6.0 interface) decides to delete a record. If the record in the
> tblAdmissions table has the primary key (AdmissionID) of "123", then
> the code below is supposed to search other tables that have related
> information in them and also have an AdmissionID of "123" and delete
> that information as well.
> Any ideas? Here's the code:
> CREATE TRIGGER tr_DeleteAdmissionRelatedInfo
> -- and here is the table name
> ON tblAdmissions
> -- the operation type goes here
> FOR DELETE
> AS
> -- I just need one variable this time
> DECLARE @.AdmissionID int
> -- Now I'll make use of the deleted virtual table
> SELECT @.AdmissionID = (SELECT @.AdmissionID FROM Deleted)
> -- And now I'll use that value to delete the data in
> -- the tblASIFollowUp Table
> DELETE FROM tblASIFollowUp
> WHERE AdmissionID = @.AdmissionID
> -- And now I'll use that value to delete the data in
> -- the tblProgramDischarge Table
> DELETE FROM tblProgramDischarge
> WHERE AdmissionID = @.AdmissionID
> -- And now I'll use that value to delete the data in
> -- the tblRoomAssignment Table
> DELETE FROM tblRoomAssignment
> WHERE AdmissionID = @.AdmissionID
> -- And now I'll use that value to delete the data in
> -- the tblTOADS Table
> DELETE FROM tblTOADS
> WHERE AdmissionID = @.AdmissionID
> -- And now I'll use that value to delete the data in
> -- the tblUnitedWaySurvey Table
> DELETE FROM tblUnitedWaySurvey
> WHERE AdmissionID = @.AdmissionID
> -- And now I'll use that value to delete the data in
> -- the tblWFGMSurvey Table
> DELETE FROM tblWFGMSurvey
> WHERE AdmissionID = @.AdmissionID|||What does "does not work" mean? Could you be a bit more specific. Why
not use cascading deletes on foreign keys for this? See the ON DELETE
CASCASE option in Books Online for details.

Your trigger will fail to delete all related rows if more than one row
is deleted from the Admissions table. Don't write triggers that way. To
do it in a trigger, try this:

CREATE TRIGGER tr_DeleteAdmissionRelatedInfo
ON tblAdmissions
FOR DELETE

AS

DELETE FROM tblASIFollowUp
WHERE EXISTS
(SELECT *
FROM Deleted
WHERE admissionid = tblASIFollowUp.admissionid)

... etc

If you need more help, please post some code that will actually
reproduce the problem, including the CREATE, INSERT and DELETE
statements (simplified if possible please).
--
David Portas
SQL Server MVP
--|||On 31 Jan 2005 08:13:54 -0800, bmccollum wrote:

>This does not work and I'm not sure why...

Hi bmccollum,

Well, "does not work" is not exactly an accurate description of what's
happening. Is the delete rejected? Is the delete accepted, but the action
that the trigger should do is not done? Do you get error messages? Is
white smoke bellowing out of your server?

>Any ideas? Here's the code:
(snip)

Based on your code, I can do a wild guess. In fact, you've got two
problems. Both are here:

>SELECT @.AdmissionID = (SELECT @.AdmissionID FROM Deleted)

First, the second @. should be left out. This will simply set the variable
@.AdmissionID equal to itself.

But if you change it to

SELECT @.AdmissionID = (SELECT AdmissionID FROM Deleted)
or
SET @.AdmissionID = (SELECT AdmissionID FROM Deleted)
or
SELECT @.AdmissionID = AdmissionID FROM Deleted

you'll still have problems. Not if you delete only one row, but you'll get
an error as soon as one DELETE operation deletes more than one row from
the admissions table. It's important to know that triggers fire once per
statement, not once per row. If three rows are deleted, the deleted
pseudo-table will hold three rows. This will cause the first two versions
of the assignment to error; the third will simply assign the value from
one of these three rows to @.AdmissionID.

Even if your present application will never delete more than one row at a
time, you should always ensure that your triggers handle multi-row
inserts, updates and deletes well. Someday, your application will be
changed...

CREATE TRIGGER tr_DeleteAdmissionRelatedInfo
ON tblAdmissions
FOR DELETE
AS

DELETE FROM tblASIFollowUp
WHERE EXISTS
(SELECT *
FROM deleted
WHERE deleted.AdmissionID = tblASIFollowUp.AdmissionID)

(etc)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||A few things to start with. Read ISO-11179, so you will stop putting
those silly prefixes on data element names. Besides violating
standards, it makes a data dictionary almost impossible to use. The
reason I call it silly is that SQL only has one data structure, so the
prefix is redundant, improper and useless all at once.

You name a thing for that it is; you do not name a thing for how it is
modeled, where it is stored, its datatype, etc. Think logical AND NOT
physical.

Do you really have tables with only one row in them? That is what a
singular name says; tables out to be collective or plural. A table is
a set, not am object instance.

Do not depend on the use of the "little snail" to identify your
parameter to the guy maintaining or porting your code. What does it
mean in the data model?

Use "SET <var> = <exp>;" instead of "SELECT <var> = .." so that you do
not create confusion and the code will port. SQL Server has a lot of
options for standard code now, so use them.

Now the real question. Why are you still thinking of procedural code
in a declarative language, like SQL? You can use DRI (declarative
referential integrity) actions to do this. Try this skeleton:

CREATE SCHEMA Foobar ..
...
CREATE TABLE Admissions -- the source of the data element
(admission_id INTEGER NOT NULL PRIMARY KEY,
...);

CREATE TABLE ASI_Followups
( ..
admission_id INTEGER NOT NULL
REFERENCES Admissions (admission_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
...);

CREATE TABLE ProgramDischarges
( ..
admission_id INTEGER NOT NULL
REFERENCES Admissions (admission_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
...);

CREATE TABLE RoomAssignments
( ..
admission_id INTEGER NOT NULL
REFERENCES Admissions (admission_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
...);

CREATE TABLE Toads -- weird name!
( ..
admission_id INTEGER NOT NULL
REFERENCES Admissions (admission_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
...);

CREATE TABLE UnitedWaySurvey
( ..
admission_id INTEGER NOT NULL
REFERENCES Admissions (admission_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
...);

CREATE TABLE WFGMSurvey
( ..
admission_id INTEGER NOT NULL
REFERENCES Admissions (admission_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
...);

Besides being easier to code, this gives the optimizer information
about the relationships among the tables, so ALL your queries improve.
It is also faster than a TRIGGER. For example, in Sybase SQL Anywhere
there would be a single occurrence of each admission_id value and
pointer chains to all the table referencing it. Updates and deletes
are almost immediate even on huge tables.

You are still un-learning procedural code -- your "tbl-" prefixes were
a good sign that your real problem is foundations. After cleaning up
SQL code for 15-20 years, I have a good set of diagnostics :)
Go to BOL and look teh DRI you need.