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.

  • 1
    You can have column0 in table2 and then make table1.column1 references column0 – Sahap Asci Apr 22 at 20:41
  • When |column0| > |column3|,we will have null column3 values, and that will throw an error due to the primary key requirement of table2. – LaTeX2enub1336 Apr 22 at 21:32
  • The problem you ask is like a xy problem I think you better ask your question as a real case scenario. – Sahap Asci Apr 23 at 14:39
  • 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. – LaTeX2enub1336 Apr 26 at 1:05
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
);
| improve this answer | |
  • 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). – LaTeX2enub1336 Apr 27 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! – LaTeX2enub1336 Apr 27 at 2:23
  • 1
    updated the answer – Sahap Asci Apr 27 at 11:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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