Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jtmk
Starting Member
5 Posts |
 Posted - 2009-01-13 : 07:26:36
|
| Hi,If we have four tables: Site, Customer, Staff and Address with three one to many relationships i.e. AddressCode (primary key) as foreign key in the other tables, how can I ensure that only that each occurrence of address must participate in only one fo the 3 relationships?Can a user defined function be used and if so how?RegardsJT |
|
|
darkdusky
Aged Yak Warrior
591 Posts |
Posted - 2009-01-13 : 07:48:09
|
| Can you clarify"each occurrence of address must participate in only one fo the 3 relationships"Can 2 people from the same house work as staff?Can staff also be customers?What about customers changing address? Can a customer move to a previous customer's address? |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-13 : 08:43:00
|
quote:
Originally posted by jtmk
Hi,If we have four tables: Site, Customer, Staff and Address with three one to many relationships i.e. AddressCode (primary key) as foreign key in the other tables, how can I ensure that only that each occurrence of address must participate in only one fo the 3 relationships?Can a user defined function be used and if so how?RegardsJT
i think what you need is to use a trigger to enforce this business rule |
|
|
|
jtmk
Starting Member
5 Posts |
Posted - 2009-01-13 : 08:56:40
|
| Each occurence of Address must participate in only one of the three raltionships Site situatedAt Address, Customer BasedAt Address, Staff LivesAt AddressSo I guess a customer address cannot also be a staff or site address and vice versa but multiple staff could live at the same address. Is there a way to globally enforce the foreign key so a check ensures that the AddressCode only appears in one of the three entities? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-13 : 09:00:52
|
quote:
Originally posted by jtmk
Each occurence of Address must participate in only one of the three raltionships Site situatedAt Address, Customer BasedAt Address, Staff LivesAt AddressSo I guess a customer address cannot also be a staff or site address and vice versa but multiple staff could live at the same address. Is there a way to globally enforce the foreign key so a check ensures that the AddressCode only appears in one of the three entities?
as i said earlier, one option is trigger existing on all three tables which checks whenever a record is inserted if value exists in other two tables and if yes it throws an exception. |
|
|
|
jtmk
Starting Member
5 Posts |
Posted - 2009-01-13 : 09:37:23
|
| Thanks for the heads up visakh16!RegardsJTMK |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-13 : 10:17:15
|
you're welcome  |
|
|
|
|
|
|
|
|
User Defined Constraint