Question 170191 in PostgreSQL Question posted in
The official documentation can be found here.

Can Postgres constrain a subset of values based on another column’s value?

In PostgreSQL I have a "cars" table (id, model, color)
How can I constrain the input color based on the model?
For example:

  • if AA model then the color must be blue, red or black
  • if BB model then the color must be blue, gray or black

Rather than using a condition in the constraint with the colors hard-coded, is it possible to rely on a global list of colors filtered based on the model?
Thanks for your help

Tags:

2

Answers


  1. 0

    Just have a table to record it

    CREATE TABLE cars (
    car_id     int
  , car_model  something FOREIGN KEY REFERENCES car_models
  , car_colour something FOREIGN KEY REFERENCES car_colours
  etc

  CONSTRAINT valid_model_colour FOREIGN KEY (car_model, car_colour) REFERENCES model_colour_combos
);

CREATE TABLE model_colour_combos (
    car_model  something FOREIGN KEY REFERENCES car_models
  , car_colour something FOREIGN KEY REFERENCES car_colours
  , PRIMARY KEY (car_model, car_colour)
);

INSERT INTO model_colour_combos VALUES
    ('AA', 'blue')
  , ('AA', 'red')
  , ('AA', 'black')
  , ('BB', 'blue')
  , ('BB', 'grey')
  , ('BB', 'black')
;
    Login or Signup to reply.
  2. 0

    Not sure I understand the question, but assume:

    CREATE TABLE model_colors
( model TEXT NOT NULL
, color TEXT NOT NULL
, PRIMARY KEY (model, color)
);

INSERT INTO model_colors (model, color)
VALUES ('AA', 'blue'), ('AA', 'red'), ...

    Now, in your cars table:

    CREATE TABLE cars
( ...
, model TEXT NOT NULL
, color TEXT NOT NULL
, FOREIGN KEY (model, color) REFERENCES model_colors (model, color)
);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search