0

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 column3column1?

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 column3column1. Again duplicate rows are not allowed.

2
  • When |column0| > |column3|,we will have null column3 values, and that will throw an error due to the primary key requirement of table2. Apr 22, 2020 at 21:32
  • 1
    To use your link's terminology, I'm not asking about Y - I already know Y works. I'm asking for a better solution to X than Y. Apr 26, 2020 at 1:05

1 Answer 1

1

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
);
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 2:23
  • 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 2:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.