Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Aug 2004
    Posts
    17

    Unanswered: 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?

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Do you mean like:

    Code:
    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
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Aug 2004
    Posts
    17
    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.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Aug 2004
    Posts
    17
    Blindman and Brett, thanks for the quick answers. It looks like I will rethink this idea and go about it a little differently.

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by blindman
    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

    Code:
    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
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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!
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by blindman
    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
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Brett Kaiser
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    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!!!
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Comment deleted upon consideration of better judgement...
    Last edited by blindman; 02-25-05 at 01:56.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  14. #14
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Quote Originally Posted by blindman
    Comment deleted upon consideration of better judgement...
    Hey, I liked the original post, before editing
    Quote Originally Posted by blindman
    Comparing it to a wife is a really unfair!
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  15. #15
    Join Date
    Aug 2004
    Posts
    17
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •