Multiple reference in a single field

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Database Design » Design Ideas and Questions » Multiple reference in a single field

Multiple reference in a single field

Rate Topic

Display Mode

Topic Options

Author

Message

sagavb

Posted Monday, April 12, 2010 4:33 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, August 31, 2012 8:13 AM
Points: 2, Visits: 13

Hi Dudes,

I need to have a foreign key field that should refer from two tables. for an example,

table_a
--------
1 a_id primary key,
2 name

table_b
--------
1 b_id primary key,
2 name

table_c
-------
1 c_id
2 name
3 key_id (i.e, it should be foreign key from either table_a or table_b. it may have value of either table_a's id or table_b's id

Can any one of u help me to get this script to create table.

Thanks
Saga

Post #901450

ChrisM@Work

Posted Monday, April 12, 2010 5:33 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:21 AM
Points: 5,602, Visits: 10,950

How will you determine which lookup table the foreign key value is from, for each row of table c?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps

Post #901489

Pulivarthi Sasidhar

Posted Monday, April 12, 2010 5:58 AM

SSC Eights!

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 5:30 AM
Points: 870, Visits: 522

hi,

I think its not possible to do......

Try to do the following one...

For this U should add another column in TableC.
Key_IDa is foreign key and references to Table A PK.
Key_IDb is foreign key and references to Table B PK.

Why dont you check with the Wizard options availed from SSMS.
For relationships btwn tables ,Creating Foreign Keys........

Smile

Thanks!
Sasidhar Pulivarthi

Post #901505

PaulB-TheOneAndOnly

Posted Tuesday, April 13, 2010 11:15 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 7:03 AM
Points: 2,979, Visits: 4,389

Sasidhar is correct.

FKs are there to enforce referential integrity rules. No referential integrity rules takes into consideration one child row with two alternative parents.

If this is a business spec it has to be manually enforced.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Post #902638

RBarryYoung

Posted Tuesday, April 13, 2010 11:46 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Saturday, May 04, 2013 11:13 AM
Points: 9,855, Visits: 9,374

You cannot enforce this with static RI (referential integrity) without the use of an artificial "helper" table, which is a bit of a mess to setup and maintian and not recommended. The recommended alternative is to use dynamic RI, (which almost always means triggers) to enforce this constraint.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc. "Performance is our middle name."

Post #902651

« Prev Topic | Next Topic »

Permissions