What are the pros and cons of a performance / indexing / data management perspective for creating a one-to-one relationship between tables using a primary key for a child as a foreign key compared to a pure surrogate primary key for a child? The first approach seems to reduce redundancy and nicely limits the one-to-one relationship, while the second approach seems to favor database administrators, although 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)
)
platforms of interest here are Postgresql, Microsoft SQL Server.
Edit:
, DBA. - . , 1-1000000 , . , ad-hoc- , , , . , , " " . , , , , , , Microsoft SQL. MS (.. PG ANALYZE PG)? , , Postgresql.