I want to create a One-To-One relationship between two tables : teachers
and office
(the teacher is the owner of the relationship).
Looking up online, I found two different approaches :
First one, to define a foreign key
in the office table and add constraints NOT NULL
and UNIQUE
. Basically, a mandatory One-To-Many relationship with the UNIQUE constraint:
Like so :
CREATE TABLE office(
id SERIAL PRIMARY KEY,
floor SMALLINT CHECK (floor > 0 AND floor <= 10),
size SMALLINT CHECK (size > 0),
teacher_id INTEGER NOT NULL UNIQUE REFERENCES teachers(teacher_id)
);
And the second one where we combine the primary key
and the foreign key
in the office table into one row. Like so :
CREATE TABLE office(
id SERIAL PRIMARY KEY REFERENCES teachers(teacher_id),
floor SMALLINT CHECK (floor > 0 AND floor <= 10),
size SMALLINT CHECK (size > 0)
);
Functionally, it looks to me like both approaches work the same. The first one seems to use a little bit of extra memory, since it has one more row.
My question is: What are the advantages and disadvantages of each approach? Is one approach "better" than the other ?
I would go with the second approach, to share the exact same value for the primary key. For the office
table the primary key should not be autogenerated (serial
) but just an int
.
For example you can do:
create table teachers (
id int primary key not null,
name varchar(20) not null
);
create table office (
id int primary key references teachers (id),
floor smallint check (floor > 0 and floor <= 10),
size smallint check (size > 0)
);
alter table teachers
add constraint fk_uq1_teachers_office
foreign key (id) references office (id) deferrable initially deferred;
Then inserting data could look like:
begin transaction;
insert into teachers (id, name) values (100, 'Mary');
insert into office (id, floor, size) values (100, 3, 850);
commit;
Notice that in order to enforce the 1-1 relationship you need to have two foreign keys: one from teachers
to office
, and one from office
to teachers
. Otherwise an application could insert data into teachers
and forget to insert into office
. Fortunately you are using PostgreSQL that implements the standard SQL feature of constraint deferrability as shown above.
See running example at DB Fiddle.