Data Relationships

Before dive into relationships, there is a need explain core concepts on how data are referenced between different tables.

Primary key

A primary key constraint indicates that a column, or group of columns, can be used as a unique identifier for rows in the table. This requires that values be both unique and not null. So, the following two table definitions accept the same data:

CREATE TABLE publisher
(
    id      INTEGER PRIMARY KEY,
    ...
);

CREATE TABLE publisher
(
    id      INTEGER NOT NULL UNIQUE,
    ...
);

Primary keys can span more than one column; the syntax is similar to unique constraint:

CREATE TABLE example
(
    a INTEGER,
    b INTEGER,
    c INTEGER,
    PRIMARY KEY (a, c)
);

Adding a primary key will automatically create a unique B-tree index on the column or group of columns listed in the primary key, and will force the column(s) to be marked NOT NULL.

A table can have at most one primary key.

Note

There can be any number of unique and not-null constraints, which are functionally almost the same thing, but only one can be identified as the primary key.

Foreign key (FK)

A foreign key constraint specifies that the value in a column (or a group of columns) must match the values appearing in some row of another table. We say this maintains the referential integrity between two tables.

A table can have more than one foreign key constraint. Foreign key can also overlaps with the primary key.

To define a FK constraint:

CREATE TABLE book
(
    id           SERIAL PRIMARY KEY,
    publisher_id INTEGER REFERENCES publisher
);

CREATE TABLE book
(
    id           SERIAL PRIMARY KEY,
    publisher_id INTEGER REFERENCES publisher (id)
);

CREATE TABLE book
(
    id           SERIAL PRIMARY KEY,
    publisher_id INTEGER,
    FOREIGN KEY (publisher_id) REFERENCES publisher
);

In this situation the book table is the referencing table, and the publisher table is the referenced table. Similarly there are referencing and referenced columns.

In case the referenced column is not explicitly defined, the primary key of the referenced table will be used as the referenced column.

It is not possible to create a row with non-null foreign key, that does not appear in the referenced table.

Self-referential foreign key

Sometimes it is useful to the referenced table to be the same table as referencing. This makes possible to create a tree-structures with nodes, there a top-level node would have NULL parent_id.

CREATE TABLE tree
(
    id        SERIAL PRIMARY KEY,
    parent_id INTEGER REFERENCES tree
);

Deleting referenced rows

So far we know that the foreign keys disallows creation of rows that do not relate to any row inside the referenced table. But what if a referenced row needs to be removed? Intuitively, there are few options:

  • disallow deletion

  • delete referencing rows as well

  • something else…

Restricting and cascading deletes are the two most common options. RESTRICT prevents deletion of a referenced row. CASCADE will also delete all referencing rows as well as the referenced one.

CREATE TABLE book
(
    id           SERIAL PRIMARY KEY,
    publisher_id INTEGER REFERENCES publisher ON DELETE RESTRICT
);

CREATE TABLE book
(
    id           SERIAL PRIMARY KEY,
    publisher_id INTEGER REFERENCES publisher ON DELETE CASCADE
);

There are others options.

NO ACTION means that if any referencing rows still exist when the constraint is checked, an error is raised; this is the default behavior.

SET NULL will replace all referencing rows foreign key values with NULL values.

SET DEFAULT will replace all referencing rows foreign key values with default values.

Relationships

From the point of view of the RDBMS there are no other relationships between two tables except the foreign key. But the way the foreign keys are designed brings us three concepts of the data relations.

One-to-many

In a relational database, two tables have a one-to-many relationship if each row in one table references multiple rows of the other table. For example publisher can publish many books, but a single book is published by some specific publisher.

CREATE TABLE book
(
    id               SERIAL PRIMARY KEY,
    title            VARCHAR(255) NOT NULL,
    synopsis         TEXT,
    isbn             VARCHAR(16),
    publisher_id     INTEGER      NOT NULL REFERENCES publisher ON DELETE RESTRICT,
    publication_date DATE,
    language         VARCHAR(64),
    page_count       INTEGER,
    keywords         TEXT
);

COMMENT
    ON TABLE book IS 'available books';
COMMENT
    ON COLUMN book.title IS 'non-zero length book''s title';
COMMENT
    ON COLUMN book.keywords IS 'whitespace separated keywords';

ALTER TABLE book
    ADD CONSTRAINT check_title_length CHECK (LENGTH(title) > 0);

DROP TYPE IF EXISTS book_genre;
CREATE TYPE book_genre AS ENUM (
    'Adventure',
    'Biography',
    'Comedy',
    'Crime',
    'Drama',
    'Fantasy',
    'Historical Fiction',
    'Horror',
    'Mystery',
    'Poetry',
    'Romance',
    'Science Fiction',
    'Self-Help',
    'Thriller',
    'Young Adult'
    );

COMMENT
    ON TYPE book_genre IS 'registered book genres enumeration';

ALTER TABLE book
    ADD COLUMN genre book_genre;

ALTER TABLE book
    OWNER TO libms;

One-to-one

Note

You can think of one-to-one relationship as a limited version of one-to-many.

In a relational database, two tables have a one-to-one relationship if each row in one table links to exactly one row in the other table, and vice versa. For example a table of members and a table of contacts might have a one-to-one relationship.

CREATE TABLE member
(
    id         SERIAL PRIMARY KEY,
    first_name VARCHAR(64) NOT NULL CHECK (LENGTH(first_name) > 0),
    last_name  VARCHAR(64) NOT NULL CHECK (LENGTH(last_name) > 0),
    dob        DATE,
    registered DATE DEFAULT NOW(),
    contact_id INTEGER UNIQUE REFERENCES contact
);

COMMENT
    ON TABLE member IS 'library registered members';
COMMENT
    ON COLUMN member.contact_id IS '1-to-1 relationship to contacts table';

ALTER TABLE member
    OWNER TO libms;

-- this is the default member to reference, in case actual member is deleted
INSERT INTO member
VALUES (0, 'Deleted', 'User', NULL, NULL, NULL);

Many-to-many

In relational database, two table have many-to-many relationship if each row in one table can reference multiple rows in another table, and vice versa. For example table book may contain records for books authored by many authors. On the other hand author can write multiple books. Implementing a many-to-many relationship requires creation of an intermediate table, that applies one-to-many relationship with both original tables.

CREATE TABLE book_author
(
    book_id   INTEGER REFERENCES book,
    author_id INTEGER REFERENCES author
);

COMMENT
    ON TABLE book_author IS 'books-to-authors relationship';
COMMENT
    ON COLUMN book_author.book_id IS 'unique together with author_id';
COMMENT
    ON COLUMN book_author.author_id IS 'unique together with book_id';

ALTER TABLE book_author
    OWNER TO libms;