this may be a pretty noob question..

does a foreign key always have to be a primary key in a table ?

if no...

can that foreign key be appended to the existing primary key... and still just let that one record by the referencing foreign key ?

example.

if i got a contact table with the primary key being an appended key of Contact Name and Contact Info.. (so that duplicates will not exist for contacts) but i wanna reference these contacts using a Contact ID which is not part of the primary key (but wanna know if i can make it a foreign key)

Any help rendered to this problem is much appreciated

thanks

I suppose that the simplest way to find answers to such questions is to simply try it - create those tables and experiment with different combinations of primary and foreign keys. Doing that, you'll learn much more than just reading what someone else says.

well the thing is...

Im in the process of drawing up my ERD... so nowhere close to coding just yet

so im just wondering from now

no

a foreign key may reference a primary key or a unique key

so in your example with the primary key of Contact Name and Contact Info, you can also have a Contact ID but you have to declare it UNIQUE in order to reference it from some other table with a foreign key

ok thanks... that helps alot...

is the second part to the initial thread possible tho ?

as is it possible to have a primary key with multiple records and use just one record from this as a foreign key to a next table

I do not think that making a small research on this topic would take you long time. Definitely less than waiting for an answer in a forum.

Anyway, you may always consult Oracle documentation, which is available e.g. online on http://tahiti.oracle.com/
For 11gR2, the definition (syntactic and semantic) of foreign key constraints is placed here: http://download.oracle.com/docs/cd/E...htm#sthref2065
(there are also other ones; you should ensure none of them is happening also)

i'm not completely sure i understand this question but i'm pretty sure the answer is yes if you substitute "column" for "records"

yes, it is possible to have a composite primary key (consisting of multiple columns) and use just one column from this as a foreign key to another table

If I understand your requirement correctly, you have master table T1 with composite primary key ( C1, C2 ) and in child table T2 you want to reference it only on a part of that primary key (say only on column C1).

In that case, it is not possible. In the foreign key definition, there is no notice about "part" - the unique or primary key constraint is stated (and meant to be) in a whole. Just think logically about it - which parent would you pick if there could (and would) be duplicates in the referenced column. The foreign key would make no sense at all, so there would be no reason for stating it.

Now you have two answers. You may pick any one of them, or just run a small test case - two CREATE TABLE statements should not take you long. In fact, you would know the answer now if you did it in the beginning.