foreign key constraints on primary key columns - issues ?
- by zzzeek
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.
