Re: General question - Mailing list pgsql-general

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.