Home » SQL & PL/SQL » SQL & PL/SQL » PK and FK one one column
PK and FK one one column [message #222271] Fri, 02 March 2007 10:39 Go to next message
miroconnect@yahoo.com
Messages: 202
Registered: April 2006
Senior Member
Can I have both PK and FK on one column in a table if so is this a good design and if not what are the disadvantages ?
Re: PK and FK one one column [message #222278 is a reply to message #222271] Fri, 02 March 2007 11:19 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Why not combine the tables to one table then?
What made you decide to split it?
Re: PK and FK one one column [message #222282 is a reply to message #222271] Fri, 02 March 2007 11:37 Go to previous messageGo to next message
miroconnect@yahoo.com
Messages: 202
Registered: April 2006
Senior Member
I am saving addtional information for exsisting service so its kind of addon functionality to exsisting service. Out framework created this service because of my custom needs I am saving custom information in this additional table.
Re: PK and FK one one column [message #222318 is a reply to message #222282] Fri, 02 March 2007 16:15 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
That's generally considered bad design in Oracle because it increases the cost of SQLs without saving any real space. The preferred method is to append the new columns to the existing table. If they are all NULL for any given record then they will take up no space.

Note that only trailing null columns take up no space. If you were to add another NOT NULL column to the table then all of those NULLs would take up one byte (I think) each in the block header. In such a case it might be worth reorganising the table to place all of the null colums at the end.

In response to your original query: yes, a column can be both a PK and a FK. Try it out.

Ross Leishman
Re: PK and FK one one column [message #222349 is a reply to message #222271] Sat, 03 March 2007 02:49 Go to previous message
dude4084
Messages: 221
Registered: March 2005
Location: Mux
Senior Member
Just want to add on the basis of my limited expereince:

"Primar key values must be Unique while Foreign Key can be repetetive".

So i am thinking that how the same column will act as FK and PK at the same time with non repetetive values? Thus the only possible situation is that where FK is working under 1-to-1 relationship.
Previous Topic: execution of procedure
Next Topic: simple but knowledgeable pls help
Goto Forum:
  


Current Time: Mon Dec 05 18:51:04 CST 2016

Total time taken to generate the page: 0.11335 seconds