Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2008 > SQL Server 2008
|
SQL Server 2008 General discussion of SQL Server *2008* version only - not related to a specific book.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2008 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old November 28th, 2010, 11:11 PM
Registered User
 
Join Date: Nov 2010
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Foreign Key to multiple tables

CREATE TABLE [dbo].[Updates](
[Update_ID] [int] IDENTITY (1,1) NOT NULL,
[User_ID] [int] NOT NULL,
[U_Description] [nvarchar](100) NOT NULL,
[U_Type] [char](1) NOT NULL,
[Item_ID] [int] NOT NULL,
[Date_Time] [datetime] DEFAULT CURRENT_TIMESTAMP NOT NULL,
CONSTRAINT pk_Updates PRIMARY KEY CLUSTERED (Update_ID),
CONSTRAINT fk_Updates FOREIGN KEY (User_ID)
REFERENCES Users (User_ID)
ON DELETE CASCADE
ON UPDATE CASCADE
,
CONSTRAINT fk_Updates2 FOREIGN KEY (Item_ID)
REFERENCES Video (Video_ID)
ON DELETE NO ACTION
ON UPDATE NO ACTION
,
CONSTRAINT fk_Updates3 FOREIGN KEY (Item_ID)
REFERENCES User_Event (Event_ID)
ON DELETE NO ACTION
ON UPDATE NO ACTION


How can i make this work? I use U_Type to know witch table i have to join & Item_ID to find the specific item..
 
Old November 29th, 2010, 05:19 PM
Friend of Wrox
 
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

You can't.

You can NEVER declare a foreign key to reference more than one table.

You *CAN* have the fields there and use them as you are doing (that is, Item_ID can be a *NUMBER* that might be in one of several different tables. And you can build queries to do what you want. But because you are violating the database design rules, you won't get SQL Server (or any other database) to help you.

Don't get me wrong: I understand that there are times when you will do things like this because the alternatives are harder to write and even uglier. But you do need to understand that you are doing something that's not supported by standard DB design.





Similar Threads
Thread Thread Starter Forum Replies Last Post
SQL Design: Foreign Key to Multiple Primary Keys? kalel_4444 BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0 10 May 8th, 2008 04:14 PM
Foreign key not updating with Primary key xavier1945 BOOK: Access 2003 VBA Programmer's Reference 2 July 4th, 2007 09:48 PM
Composite Foreign Key bijgupt SQL Server 2005 6 May 4th, 2007 08:46 AM
FOREIGN KEY and PRIMARY KEY Constraints junemo Oracle 10 June 15th, 2004 01:00 AM
Foreign Key Problem re_anu SQL Server 2000 1 September 26th, 2003 10:58 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.