Foreign Key Urgent Help

Question

1.00/5 (1 vote)

See more: (untagged)

If you refer a composite primary key of a master table, you have to refer all the fields of the composite primary key.
But how to partially refer the composite primary key in a child table? For example, if Table1 has primary key (A,B,C),
how to create a foreign key in Table2 which will refer only (A,B) fields of Table1?

thanks in advance,

Posted 26-Mar-13 8:27am

srastogi85

Updated 26-Mar-13 8:28am

Zoltán Zörgő

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

**Zoltán Zörgő** · Accepted Answer · 2013-03-26T08:34:00

Solution 1

You can do this, but:
1) You can add reference to a property set that is unique.
2) As primary key, (A,B,C) is unique.
3) If you want to add reference to (A,B), it has to be unique
4) If (A,B) is unique thus a primary key candidate, why is (A,B,C) the primary key after all?

I smell a design problem. Please consult the following article too: http://www.sqlmag.com/article/systems-administrator/sql-by-design-how-to-choose-a-primary-key-5113[^]

Posted 26-Mar-13 8:34am

Zoltán Zörgő

Comments

srastogi85 26-Mar-13 14:41pm

Thanks Zoltán
, i already had table structure having (A,B) as primary and referecned by other tables, requirement is i added 1 new table, for this new table i needed one more Value so introduced column C, primary key made as (a,b,c) ,and wanted the other relations to work existing ones with (A,B) to work as earlier.
Please suggest .

Zoltán Zörgő 26-Mar-13 14:45pm

Why do you need this one more value? But it is your choice.
If (A,B) is still key candidate, do following:
1) let (A,B) be the primary key
2) create a unique constraint over (A,B,C)
3) use the (A,B) as referenced property set in the one, and (A,B,C) in the other table

srastogi85 26-Mar-13 14:51pm

Thanks again , but the table where i am now going to make (A,B,C) as primary key i will be
saving multiple rows, which will have a,b,c as unique
Exact tables mentioned below:-
Table 1 -> Column A as foreign key
Table 2 -> column A,B vas primary key
Table 3 ->column A,B as foreign key
Table 4 ->column A as foreign key

Zoltán Zörgő 26-Mar-13 15:10pm

Than use (A,B,C) as primary key, and add create a unique constraint over (A,B). But I am pretty sure you have a design problem over there.

Maciej Los 26-Mar-13 15:06pm

Nice, +5!

Zoltán Zörgő 26-Mar-13 15:09pm

Thank you.

srastogi85 26-Mar-13 15:24pm

Thanks for the Help
but i need to change the logic as you suggested initially some design fault.
will try to modify the solution accordingly.
As when it gets unique
we might have key as (a,b,c) ,(a,b,d)
and unique contraitn will be (a,b) (a,b) which will cause the issue , will see now for alternale solution

Zoltán Zörgő 26-Mar-13 15:50pm

Be aware of the concept: tables represent entities, the entity is referenced by an other entity (and the entity has only one primary key); and not attributes are referenced by attributes. The only case where I can imagine your situation is the following: if your goal is to restring the referencing table to be able to refer only a subset of the first table.