Database Administration
postgresql rdbms referential-integrity composite-types
Updated Sat, 30 Jul 2022 17:37:53 GMT

Better way to ensure referential integrity of one column to a composite primary key?


Given a first table 

CREATE TABLE table1 (
   column1 INTEGER,
   column2 TEXT,
   PRIMARY KEY (column1, column2));

does SQL have an idiomatic way to constrain column3 in a second table

CREATE TABLE table2 (
    column3 INTEGER,
    column4 TEXT,
    PRIMARY KEY (column3, column4));

such that column3 column1?

My current solution is this kludge:

CREATE TABLE table0 (
    column0 SERIAL PRIMARY KEY);
CREATE TABLE table1 (
   column1 INTEGER REFERENCES table0 (column0),
   column2 TEXT,
   PRIMARY KEY (column1, column2));
CREATE TABLE table2 (
    column3 INTEGER REFERENCES table0 (column0),
    column4 TEXT,
    PRIMARY KEY (column3, column4));

I learned the hard way that a foreign key cannot refer to only one value of a composite primary key: https://stackoverflow.com/questions/3996774/foreign-key-relationship-with-composite-primary-keys-in-sql-server-2005.

I'm using PostgreSQL, if that matters.

Edit (2020-04-26)

One comment recommended I add a concrete example.

The table table1 contains aliases. Multiple people can be named 'Jane Doe', and any one 'Jane Doe' can also be called 'Jane Smith' and 'Jane1990'. Each person is uniquely identified by the number in column1. Either column can have duplicates, but duplicate rows are not allowed.

The table table2 lists books published by people in table1, but table1 can include people who have never published a book, and so column3 column1. Again duplicate rows are not allowed.

Solution

In some complex domains (banking, telekom) name is always an entity not an attribute. An individual can have more than one passport (names, nationality) I would create a table structure for the scenario you provide such;

create table author (
  id integer PRIMARY KEY,
  default_name_id integer -- you may / may not need this
  -- , any other author properties
);
-- just name aliases
create table author_name (
  id integer PRIMARY KEY,
  author_id integer NOT NULL REFERENCES author(id),
  name text not null,
  unique (author_id, name)
);
alter table author add FOREIGN KEY (default_name_id) REFERENCES author_name (id);
create table books(
  id integer PRIMARY KEY,
  book_name text
);
-- a book can be published by more than one author
create table book_publisher (
  book_id integer    REFERENCES books (id),
  author_id integer  REFERENCES author (id),
  PRIMARY KEY (book_id, author_id)
);

Postgresql have array types. Its also very useful. However most of ORM's doesn't support it. If you can use arrays in your application code then its simpler; 

create table author (
  id integer PRIMARY KEY,
  name text[],  -- name[1] is default name.
  -- , any other author properties
);




Comments (2)

  • +1 – Clever - I like how you added the foreign key to table author with ALTER TABLE. It means we'll never delete a person's default name from the list of aliases without getting an error. Table author_name fails a requirement by allowing duplicate combinations of author_id and name, but we can fix that by constraining author_id to NOT NULL and adding UNIQUE (author_id, name). — Apr 27, 2020 at 02:23  
  • +0 – The kludge I used for a solution seems to be idiomatic SQL, then. To limit values in some column A to a subset of values in a column B that allows duplicates, we must create a column C identical to column B but with all duplicates removed. Accepting your answer as the solution. Thank you! — Apr 27, 2020 at 02:23  

External Links

External links referenced by this document:

References