Quick Question on Relationships

Hopefully, this is an easy one for people with more experience with SQL than me...

Is it possible to create a foriegn key relationship between two tables in SQL that relates one column in the primary table to two columns in a secondary table?

For example, say you have a table with user account information, and you want to store some type of interaction (some type of correspondence, for example) between two users. The second table (used to store interaction), among other things, stores the primary keys that indentify the two user profiles (in this scenario, say the primary key for the users is a single column surrogate key). So you store both user's keys in the second table in their own columns (one to note who started the correspondence, the second to note who the correspondence was sent to).

Is there a way to define (in my mind, I see it as two) relationships that would relate the primary key column to both columns in the second table so you can cascade a delete? Enterprise Manager for SQL2k won't let you do this (at least that I can find).

Is there the type of scenario that a Trigger would come into play?Do you mean like:

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myUsers99(UserId int PRIMARY KEY, UserType varchar(10))
CREATE TABLE myNotes99(UserId1 int, UserId2 int, Notes varchar(255)
, FOREIGN KEY (UserID1) REFERENCES myUsers99(UserId)
, FOREIGN KEY (UserID2) REFERENCES myUsers99(UserId)
)
GO

SET NOCOUNT OFF
DROP TABLE myNotes99
DROP TABLE myUSers99
GO|||It's called a Many-To-Many relationship.

The values in each dataset can be related to more than one value in the other dataset through an intermediary table. In your case, both datasets are the same table.|||Exactly, except I want to cascade deletes to the child table. If a user is deleted from myUsers99 then any rows in myNotes99 that reference that user ID will cause errors in the code. If you select the Cascade Delete checkbox, then Enterprise Manager throws an error when saving the changes that it creating the contraints "may cause cycles or multiple cascade path", which is actually what I want.

I could do this pretty easily through code, but I have become a fan as of late of moving as much RI maintenance into the database layer instead of runtime.|||Very dangerous. You better be damn sure of your record relationships.

You are best off doing this using a stored procedure to delete your records, but if you insist on having it tied to the schema then you could do it in a trigger.|||Blindman and Brett, thanks for the quick answers. It looks like I will rethink this idea and go about it a little differently.|||Very dangerous.

Ummm...maybe...

I never use cascading updates or deletes, and I do prefer to do all the work in a stored procedure.

As a matter of fact, I prefer to totaly isolate developers to only have EXEC authority to sprocs, so I know that no errant DML will cause any data integrity issues.

Also, the fact of having as much constraints isloated to the database the better...

So without furth ado...my first INSTEAD OF TRIGGER

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myUsers99(UserId int PRIMARY KEY, UserType varchar(10))
CREATE TABLE myNotes99(UserId1 int, UserId2 int, Notes varchar(255)
, FOREIGN KEY (UserID1) REFERENCES myUsers99(UserId)
, FOREIGN KEY (UserID2) REFERENCES myUsers99(UserId)
)
GO

CREATE TRIGGER myTrigger99 ON myUsers99 INSTEAD OF DELETE
AS
BEGIN
DELETE FROM myNotes99
WHERE UserID1 IN (SELECT UserId FROM deleted)
OR UserID2 IN (SELECT UserId FROM deleted)

DELETE FROM myUsers99
WHERE UserId IN (SELECT UserId FROM deleted)
END
GO

INSERT INTO myUsers99(UserId, UserType)
SELECT 1,'Manager' UNION ALL
SELECT 2,'Client' UNION ALL
SELECT 3,'Scrub'
GO

INSERT INTO myNotes99(UserId1, UserId2, Notes)
SELECT 1,3,'Get to work' UNION ALL
SELECT 1,2,'Have a nice day' UNION ALL
SELECT 1,1,'Note to self...fire scrub'
GO

SELECT * FROM myNotes99
GO

DELETE FROM myUsers99 WHERE UserID = 1
GO

SELECT * FROM myUsers99
GO

SELECT * FROM myNotes99
GO

SET NOCOUNT OFF
DROP TRIGGER myTrigger99
DROP TABLE myNotes99
DROP TABLE myUSers99
GO|||NEVER use cascading updates and deletes?

Would you NEVER use a self-cleaning oven?

NEVER use cruise control?

NEVER use your home's thermostat?

Implementing cascading updates and deletes doesn't preclude you from limiting user access only to stored procs. It just means you don't have to duplicate built-in functionality with custom code!|||NEVER use cascading updates and deletes?

No. Cascading Updates. Wouldn't you consider that a new Entity? Wouldn't you want to keep history? Oh, wait, you're in the surrogate camp? Yes

Would you NEVER use a self-cleaning oven?

Never...the wife does though

NEVER use cruise control?

Actually, no, I don't...I live in the New York Metro Area...not a chance

NEVER use your home's thermostat?

I assume you mean a programmable one...I haven't figured out to program yet...(I defer to the missus...what say would I have over what the temp was)

Implementing cascading updates and deletes doesn't preclude you from limiting user access only to stored procs. It just means you don't have to duplicate built-in functionality with custom code!

For Key information only...And wasn't your earlier point that it was dangerous?

And whether it was with a trigger, constraint or a sproc, what's the difference?

OK, Here's one. Any inadvertant DML and with cascading, you could blow away an entire relational tree...ooops...

I guess you could always have a recovery procedure if you kept history|||And whether it was with a trigger, constraint or a sproc, what's the difference?the difference is how much time you have to spend writing it

:)|||Ha ha! I knew that post would provoke a heated response! ;)

As far as your comment about inadvertently blowing away a whole relational tree, if you duplicate cascading deletes in your stored proc then you may have shifted that danger, but you haven't eliminated it.|||Relying on CASCADE ON <whatever> is like believing that when you tell your wife to fill up the car, you can hope that she would fill up the windshield wipers flued which light has been on for the past 2 weeks. But guess what, - SHE DOESN'T!!! I guess the pont is that this method is for lazy DBA's, but I guess I am even lazier, which means I don't want to remember 6 months down the road that "there was a CASCADE ON DELETE last time I checked, I swear..." WRITE ONCE, REVISIT...NEVER!!!|||Comment deleted upon consideration of better judgement...|||Comment deleted upon consideration of better judgement...Hey, I liked the original post, before editing :D
Comparing it to a wife is a really unfair!|||Wow...lots to chew on!! Thanks for everyone's input! In this particular case, I am not really concerned with maintaining a history because this data is not very important. So if it gets lost its not a big deal and broken relationships will case problems because the hierarchy being consistent is pretty important for this (lesser of two evils).

In frankness, the user table will almost never have a row (user) deleted. Which is all the more reason to try and plan this in the DB rather than through a SP so programmers don't have to remember that they have to clean up such-and-such table if they delete a row from so-and-so table.

I guess if I used the deleted table method as Brett suggested I could maintain RI (just have to check both tables for messages instead of one) and set some type of scheduled batch to clean up that table from time to time ...just seems like a waste of space to lug around data that shouldn't ever be used in daily operation of the code.

I agree with blindman. Why re-invent the wheel and maintain RI through SP, Triggers, or code when the database can handle it for you automagically? Some may find it lazy (and I agree that it is if you over-rely on it), but its great most of the time.

I know MS probably has a good reason for not allowing this, but it just seems like it would a nice feature (granted, it would probably rarely be used) to be able to link one column in a table to two in another and be able to cascade deletes or updates.|||Well you have a special case, and that's one of the ways to do it.

But Cascading for me has a bigger philisopgical issue. Updates especially. You are not just messing with data.

You're messing with the key.

So if the key of a state table for New Jersey is NJ, and you want to change that key with a cascade update to all of it's children to XX, sure it's easy, but does it make sense?

Wouldn't NJ "live" historically? Wouldn't XX be a brand new entity?

While this thought doesn't apply to Deletes, Deletes are just dangerous. You want to delete a parent and all of it's children, fine, take care of it in a sproc.

MOO|||Wouldn't NJ "live" historically? only in your mind

BARK|||I don't think it would exist.

Example (admittedly very hypothetical, but go with it): If 'NJ' is being updated in your database to be something like 'N.J.' (another example is what happens if New Jersey changes its state name to 'Jersey' and the abbreviation becomes something like 'JY'), then wouldn't you want your child tables to cascade the change? If your code uses the foriegn key in the child table to look up the parent row to get the full name of the state, and you didn't cascade the update, then your code is now broken (or has to be re-written to handle the new state abbreviation...neither is an acceptable option in my opinion).

If the keys are important enough to define a relationship for, I personally think they should be important enough to cascade deletes and updates (assuming of course that the two keys are actually used *in practice* to reference parents/children and have the possibility of changing or being deleted...I know there will be exceptions to that thought). Otherwise, why use a relationship?

In my experience, it saves many-o-bottle of aspirin if the database maintains itself rather than the developers.