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 |
|
nbourre
Starting Member
14 Posts |
 Posted - 2008-09-10 : 16:21:16
|
Hi,I would like to know how to create a constraint which check if the foreign key is a member in another table. Hard to follow...Let me explain my problem with 3 tables which represent the relationships between a bill, a business customer and a contact in the business.A bill is sent to a business customer and to a contact that works in the business.A business can have one or more contacts (employees).business(pkBusinessID, ...)contact (pkContactID, fkBusinessID, ...)bill (pkBillID, fkBusiness, fkContactID, ...)[URL=http://img170.imageshack.us/my.php?image=sshot162rc4.jpg] [/URL]Ok now, how do I create a constraint that make sure that the fkContactID in the bill table is member of the fkBusinessID?Thanks in advance for your help! |
|
|
nbourre
Starting Member
14 Posts |
Posted - 2008-09-11 : 09:55:29
|
| anybody?NickBeginning with something is a good start! |
 |
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2008-09-11 : 19:33:14
|
| Your diagram does not make complete sense, so I'll take a guess. Either you need to include fkBankID as part of the PK on contact, or have another table that relates the contacts to the bankID and use the surrogate key there for your bill.Generally speaking though you would not store the BankID in the BILL table because you can infer the bank from the contact. If this is not the case then you need to rething your model to releft what you actually need. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-09-11 : 19:56:38
|
| One solution would be to create a unique contraint on the contact table that included the pkContactID and fkBusinessID columns, and then create a FK constraint on the bill table to have columns (fkContactID, fkBusiness) reference the (pkContactID, fkBusinessID) columns in the contact table.You can read this blog post and the feedback posts for a discussion of various options for solving this problemhttp://weblogs.sqlteam.com/jeffs/archive/2008/08/13/examining-database-primary-keys.aspxCODO ERGO SUM |
|
|
|
nbourre
Starting Member
14 Posts |
Posted - 2008-09-11 : 21:11:42
|
| Bill.fkBankID is non nullable, but fkContactID is. The bill always belong to a bank which are the company clients, but it is not necessary to have a contact in the bank.I will check the blog post later on.NickBeginning with something is a good start! |
|
|
|
|
|
|
|
|
Constraint between two tables