foreign key constraints on primary key columns - issues ?
Posted
by zzzeek
on Stack Overflow
See other posts from Stack Overflow
or by zzzeek
Published on 2010-06-03T15:34:45Z
Indexed on
2010/06/03
15:44 UTC
Read the original article
Hit count: 268
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.
© Stack Overflow or respective owner