foreign key constraints on primary key columns - issues ?

Question

What are the pros/cons from a performance/indexing/data management perspective of creating a one-to-one relationship between tables using the primary key on the child as foreign key, versus a pure surrogate primary key on the child? The first approach seems to reduce redundancy and nicely constrains the one-to-one implicitly, while the second approach seems to be favored by DBAs, even though it creates a second index:

create table parent (
     id integer primary key, 
     data varchar(50)
)

create table child (
     id integer primary key references parent(id), 
     data varchar(50)
)

pure surrogate key:

create table parent (
     id integer primary key, 
     data varchar(50)
)

create table child (
     id integer primary key,
     parent_id integer unique references parent(id), 
     data varchar(50)
)

the platforms of interest here are Postgresql, Microsoft SQL Server.

Edit:

So here is the basic idea from an actual DBA. The main concern is index fragmentation on the child table. Suppose records with primary keys 1-1000000 are inserted into the parent table, nothing in the child table. Later, ad-hoc operations begin to populate the child table with rows that correspond to those in the parent table, but in a random order. The concern is that this will cause page splits on inserts, cause index fragmentation, and cause the "swiss cheese" effect for deletes. I will admit that these are not terms I am deeply familiar with, and when googling for them, the hits seem to be all Microsoft SQL server related. Are these MS-specific concerns (i.e., does PG's ANALYZE and such mitigate the issue on PG)? If so then this is yet another reason to use a database like Postgresql.

Answer 1

If it's a strict 1-1 relationship, I see no reason not to use the first option.

The second option provides some flexibility to make it a 1-many relationship later though, which is probably why DBAs might favor that option.

Answer 2

First, if you have a 1:1 relationship, there is no problem with the primary key of a table also representing a foreign key to another table and in fact I would suggest that this is the preferred approach.

Second, with any 1:1 relationship, the first question should obviously be whether the relationship is needed as typically you can simply include the columns in the child table into the main table. That said, there are times when a 1:1 relationship obviously makes sense.