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

composite-typespostgresqlrdbmsreferential-integrity

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.

Best Answer

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
);
Related Question