Re: General question

The main significant advantage that NOT making the primary key also a
foreign key is that you can set the foreign key reference to ON DELETE SET
NULL. If they are shared this will not work since a primary key cannot be
NULL.

However, if you are going to do "ON DELETE CASCADE" anyway then the main
question is whether and why you have the second table. From a data
semantics stand-point if the two tables truly represent the same thing but
for some reason need to be separated then using a shared key reinforces that
fact.

If you apply referential integrity then no data anomalies can exist;
semantic anomalies always can if you do not understand what is being modeled
but simply using a 1-1/shared-key does not make the model invalid.

As an example: much of my work is dealing with external systems. When I
import data from those systems I store that information onto its own table
(.core). Often I have a need to generate additional data (.extended) related
to the original. In those cases I'll often do a shared key. Both the core
and extended record represent the same entity but I place the data onto two
tables since one represents original source data and one represents
calculated data. If the original source record goes away I have no context
for the extended data and if a new source record is inserted the process by
which I do the insert regenerates the extended data. Thus it is not
necessary to keep the extended record in place.

However, there are some occasions where I generate extended data that does
want to outlive the deletion of the source record. This occurs often if the
source record is able to be changed. For simple requirements I'll just
delete the original source record and then insert the changed record. I
then have/need a process to re-link the new source with the original
extended record. In this case the extended data is not system generated but
user generated (so it cannot be refreshed automatically). Also, It is
possible that the changed source record no longer matches up with the
existing extended data and so an automatic relinking process is not
desirable. I call those unlinked foreign table records "Orphans".

If you are unsure, using a different field for the Foreign Key is more
flexible, and you can always hide much of the complexity behind a view, but
using a shared key has the advantage of clearly showing that the two tables
represent properties for identical entities but that some meta-data like
factor necessitates keeping the data on two separate tables (otherwise you
should just put them onto the same table).

David J.

From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of salah jubeh
Sent: Wednesday, March 23, 2011 8:18 AM
To: pgsql
Subject: [GENERAL] General question

Hello,

Some times the primary key is the same as the foreign key such as in the
following design. which is used to model 1-1 relationship.

In the database books, such as database fundamentals(Masri), the 1-1
relation is modeled by having two separate key.

when this kind of design (shared key) is preferable and is there any
anomalies to it.

create table Table1

( T1_Id integer not null primary key

, T1_Data varchar(9) not null

)

create table Table2

( T2_Id integer not null primary key

, T2_Data varchar(37) not null

, foreign key (T2_Id) references Table1 (T1_Id)

)

Thanks in advance