CODEWITHSUNDEEP
postgresql
samol
samol

Reputation: 20590

Foreign key constraint with a "where"

Is it possible to have a foreign key constraint with a where?

CREATE TABLE child_version_2_parent_data (
  child_id SERIAL PRIMARY KEY,
  ...some other meta data...
)

CREATE TABLE child (
  child_id SERIAL PRIMARY KEY REFERENCES parent WHERE version = 2,
  version int
)

I have a table where there are about 100 million rows (version 1 - basically legacy rows) that don't have a parent. And in the next version, all the new rows will have a parent row

Upvotes: 0

Views: 67

Answers (2)

SingleNegationElimination
SingleNegationElimination

Reputation: 156188

the way I would express this is to allow the child_id column to be null, but with a references parent, and to enforce the "correctness", add an additional check constraint (version < 2 OR child_id is not NULL)

Upvotes: 1

Colin vH
Colin vH

Reputation: 537

The technically correct way to represent your data is to have different tables for different versions.

Upvotes: 0

Related Questions