skip to Main Content

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

2

Answers


  1. 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. 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