Home » SQL & PL/SQL » SQL & PL/SQL » Can foreign key take role of primary key?
Can foreign key take role of primary key? [message #393488] Mon, 23 March 2009 01:35 Go to next message
ashreddy
Messages: 19
Registered: March 2009
Junior Member
Hi,

I have requirement where in I have to design a table name some TEMP which should have ID column as Primary key and No as another column.

My question is can we define a foreign key constraint on No column refrencing the ID from the same table TEMP

i.e.
CREATE table TEMP(ID NUMBER(5),No NUMBER(8),Name VARCHAR2(20), 
CONSTRAINT PK_temp_Id PRIMARY KEY (Id),
CONSTRAINT Fk_temp_Id Foreign Key (No)
	  References temp(Id) ) ;


I am able to create the table but I want to know,will there be any restrictions by referring from the table.

Please let me know the real scenario where-in I will get any error by defining so.

Thanks,
Ash


[EDITED by LF: applied [code] tags]

[Updated on: Mon, 23 March 2009 03:05] by Moderator

Report message to a moderator

Re: Can foreign key take role of primary key? [message #393492 is a reply to message #393488] Mon, 23 March 2009 01:44 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
This is called a pig's ear and is a valid design.
Think about the employee-manager relationship in the EMP table.
Re: Can foreign key take role of primary key? [message #393503 is a reply to message #393488] Mon, 23 March 2009 02:19 Go to previous message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

What happens when you tried?

Regards
Michel
Previous Topic: PLS-00114: identifier too long error (merged)
Next Topic: Creating Temporary tables on fly
Goto Forum:
  


Current Time: Thu Feb 13 11:05:21 CST 2025