Wednesday, July 22, 2015

Enforcing one-to-(zero or one) Relationships in a RDBMS

Overview

The following sections outline approaches one might take to enforce a one-to-(zero or one) relationship in a relational database.

Approach 1: Store the data in the same table.

This is pretty self-explanatory. If the data is truly 1:(0..1) there is no logical reason the data can't coexist in one table. Just add the additional columns that would go in the child table to your parent table and make them nullable.

However, you may actually want to separate your data into multiple tables. This could be because you want to conform to a specific model (the tables represent different entities), or because you have certain physical constraints to consider (limited space, the parent table is already as wide as you're willing to make it, etc.). There are certainly other ways to accommodate this scenario...

Approach 2a: Use a foreign key constraint.

When you create your child table (the (0..1) part of the relationship), add a foreign key column that references the primary key of the parent table. By marking the foreign key column NOT NULL and UNIQUE, you ensure that all records in the child table must reference one (and only one) record in the parent table. The resulting SQL will look something like this:

CREATE TABLE Parent (
    ParentID INT IDENTITY(1,1) NOT NULL,
    ...
    CONSTRAINT PK_Parent PRIMARY KEY (ParentID)
)

CREATE TABLE Child (
    ChildID INT IDENTITY(1,1) NOT NULL,
    ParentID INT NOT NULL UNIQUE,
    ...
    CONSTRAINT PK_Child PRIMARY KEY (ChildID),
    CONSTRAINT FK_Parent_Child_ParentID FOREIGN KEY (ParentID) REFERENCES Parent (ParentID)
)

Approach 2b: Use the same primary key.

This is sort of a twist on the previous approach. You can actually eliminate an extra column in the child table by folding the primary key and foreign key into one column. This can be accomplished with the following SQL:

CREATE TABLE Parent (
    ParentID INT IDENTITY(1,1) NOT NULL,
    ...
    CONSTRAINT PK_Parent PRIMARY KEY (ParentID)
)

CREATE TABLE Child (
    ParentID INT NOT NULL,
    ...
    CONSTRAINT PK_Child PRIMARY KEY (ParentID),
    CONSTRAINT FK_Parent_Child_ParentID FOREIGN KEY (ParentID) REFERENCES Parent (ParentID)
)

Approach 3: Use an intersection table.

The previous approaches work well for 1:(0..1) relationships, where the "parent" entity always exists, but the "child" may or may not exist. But sometimes we can have two independent lists of entities that are occasionally related, and you want to limit them so that an entity in table A cannot be related to more than one in table B, and vice-versa. In other words, it is a (0..1):(0..1) relationship.

In this scenario, the best approach is to use an intersection table. This intersection table will have two UNIQUE, NOT NULL columns that reference the primary keys of the two entity tables. The SQL to create this schema should look something like this:

CREATE TABLE TableA (
    ID INT IDENTITY(1,1) NOT NULL,
    ...
    CONSTRAINT PK_TableA PRIMARY KEY (ID)
)

CREATE TABLE TableB (
    ID INT IDENTITY(1,1) NOT NULL,
    ...
    CONSTRAINT PK_TableB PRIMARY KEY (ID)
)

CREATE TABLE Intersection (
    TableAID INT NOT NULL UNIQUE,
    TableBID INT NOT NULL UNIQUE,
    CONSTRAINT FK_Intersection_TableA_TableAID FOREIGN KEY (TableAID) REFERENCES TableA (ID),
    CONSTRAINT FK_Intersection_TableB_TableBID FOREIGN KEY (TableBID) REFERENCES TableB (ID)
)

What about true one-to-one relationships?

Utilizing Approach 1 from above seems to be the only real solution to this (although, in this instance, the "child" columns should be non-nullable). The other approaches won't work here, because there's no way to split the entities into two separate tables and relate them in a true 1:1 fashion. Attempting to do so would create a sort of chicken-and-egg situation where you can't insert data into either table. The easiest course of action is to use a single table and call it a day.

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.