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 |
|
lj
Starting Member
1 Post |
 Posted - 2007-08-08 : 09:15:49
|
| Hi everyone,This is probably a very easy question:I have a shopping cart table and two products tables (legacy reasons...).I need to create a relationship between the ShoppingCart table and the two products tables such that no products are entered in the Cart which don't exist in either product table 1 or product table 2.Any ideas?Thanks heaps,LJ. |
|
|
chrisrock
Starting Member
12 Posts |
Posted - 2007-08-13 : 14:37:01
|
| Maybe someone here knows better than me but I don't believe you can have a foreign key reference to more than one table (which is what you're looking for).However you can add an "instead of" trigger to check those tables before adding the recording. This is just one idea. create trigger CheckForExistingProduct on shoppingcartinstead of insertasinsert into dbo.shoppingcart(cartid, productid, quantity)select i.cartid, i.productid, i.quantity from inserted i left join products p1 on i.productid = p1.productid left join products2 p2 on i.productid = p2.productidwhere (p1.productid is not null or p2.productid is not null) |
 |
|
|
|
|
|
ShoppingCart Products relationships