January 24, 2002 at 3:18 pm
Hi All,
I have 2 tables with a 1-to-1 mapping. I want to be able to set a constraint which contains columns from both tables. How can I do this elegantly? I suspect using an INSERT/UPDATE trigger will do the trick, but how do I structure it?
Thank you,
Joe
January 24, 2002 at 3:48 pm
You can set a FK between them, but you would need to set a unique index on both tables to ensure that it is not a 1:many.
Steve Jones
January 24, 2002 at 5:57 pm
I like Steve's suggestion best, but from a trigger perspective, you could use IF EXISTS against the table to see if there is already a record with the matching criteria. If you are using SQL 2K, an INSTEAD OF trigger allows you to prevent the INSERT or UPDATE. If you are using SQL Server 7, you can ROLLBACK TRAN (though you have to be careful, because it will rollback ALL transactions).
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
January 25, 2002 at 9:42 am
Yikes, I hate triggers. If you must, Brian's suggestion is easier, but ift may come back to haunt you if the requirements change.
Steve Jones
January 25, 2002 at 10:32 am
I don't like the trigger method much either because of the extra overhead from executing the trigger, if nothing else. Worrying about caching and/or recompiling and the rest that goes along with it. The unique index really sounds like the way to go. Simpler is better.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply