How to best implement a 1:1 relationship in a RDBMS?

Question

Yesterday while working on a project I came up on a peculiar 1:1 relationship which left me wondering - how to best implement this (clearly, we had done it wrong :D)

The idea is that there are two types of entities, A and B. They can each exist on their own just fine, but they can also have a link between them. If there is a link, then it must be a 1:1 link, and work both ways.

It's like a bottle and a cap. They can exist apart, but when coupled together the bottle will have just one cap, and the cap will be attached to just one (and the same) bottle.

How would you implement this relationship while keeping in mind all the best practices about normalization, data integrity, etc?

Added: Almost forgot to say - they each have more than a dozen properties, so putting them in the same table with half of the fields being NULL is a pretty awkward solution. Also, the link can be broken and recreated with another entity at any time.

Answer 1

To solve this, I would start with the standard many-to-many relationship layout.

TableA
  AId
  AInfo

TableB
  BId
  BInfo

TableA2B
  AId
  BId

I would then use triggers, unique indexes, constraints to enforce the 1:1 relationship in the table. The exact method would depend on the system needs.

The reason I like this format is that many relationships have attributes as part of the relationship which are not part of the entities. This pattern allows for that now and in the future.

For example: a person works for a company. The relationship has a hire date which does not fit with the person entity or the company entity.

Answer 2

I think the schema would look like this:

create table A (
    A_id    integer    primary key,
    ...
);

create table B (
    B_id    integer    primary key,
    A_id    integer    references A (A_id),
    ...
);

alter table B add constraint c1 unique(A_id);

B can only reference one row in A, and since the field is unique, A can only be referenced by one row in B.

B.A_id is nullable, so rows can exist in A and B that don't reference each other.

The unique constraint doesn't preclude multiple NULL records existing. A unique constraint ensures that the values are all either unique, or NULL.

Answer 3

CREATE TABLE A (id INT NOT NULL PRIMARY KEY, field1, …)

CREATE TABLE B (id INT NOT NULL PRIMARY KEY, field1, …)

CREATE TABLE AB (aid INT NOT NULL, bid INT NOT NULL,
                CONSTRAINT pk_ab PRIMARY KEY (aid, bid),
                CONSTRAINT ux_a UNIQUE (aid), 
                CONSTRAINT ux_b UNIQUE (bid),
                CONSTRAINT fk_aid_a FOREIGN KEY (aid) REFERENCES A,
                CONSTRAINT fk_bid_b FOREIGN KEY (bid) REFERENCES B
                )

Answer 4

Nullable foreign key with a unique constraint at one or both ends depending (at boths ends is interesting!)

Answer 5

Separate join table of foreign key referencing A against foreign key referencing B, both columns with UNIQUE constraint. So either a link between two entities exists and is the only link for either of them, or no link exists so there's no row in the table.

Answer 6

I would use the solution proposed by Darryl:

TableA
  AId
  AInfo

TableB
  BId
  BInfo

TableA2B
  AId
  BId

and then just add unique constrain on AId in tableA2B and BId on tableA2B

alter TableA2B add constraint ucAId unique(AId)
alter TableA2B add constraint ucBId unique(BId)

I think that would solve your problem

The tableA entries that are not linked to any tableB entries would simply not be present in the TableA2B similarly tableB entries not linked to tableA.

The constrains would enforce maximum one link from tableA to tableB or tableB to tableA

Answer 7

There are IMO two different cases to consider. Case one is best pictured with a monogamous marriage: the two objects are created independendly, and at some point in time, they are joined; later, they might be detached, and possible joined with other objects. For such a relationship, I'd propose the A2B table approach used by many others here.

Case two is pictured with twins: if the two objects are connected, they are so since birth and they are so till one of them dies. For that case, you could choose to simply give both of them the same primary key during creation (e.g. in Oracle by selecting one value from a sequence and using it as ID for both tables).

Answer 8

"A foreign key at both ends. It's also awkward when you try to establish/break a link. Has to resort to triggers 'n stuff. I was hoping for a more elegant weapon."

You will not find such an elegant weapon in the universe of SQL-based systems.

Darryl Peterson's response shows the solution that is logically correct. But some cases of "changing a link" can become a nightmare in SQL due to its lack of support for the TTM concept of "multiple assignment".