PostgreSQL custom CHECK on column

Question

I have the following table:

DROP TABLE IF EXISTS employees;
CREATE TABLE cars (
    model VARCHAR (50),
    brand VARCHAR (50),
    price INTEGER
);

Which looks like the following:

model    brand        price
Clio     Renault      3000
Clio     Renault      2700
Polo     Volkswagen   4400
Golf     Volkswagen   3400

I want to perform a CHECK (or other) operation to guarantee that a model cannot have multiple different brands. I am not sure how to insert information from another column when performing the check statement for model.

Answer 1

You can enforce your constraint with one additional step of normalization:

CREATE TABLE car_model (
  model text PRIMARY KEY
, brand text NOT NULL
);

CREATE TABLE car (
  car_id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
, model  text NOT NULL REFERENCES car_model
, price  integer
);

Now, the PRIMARY KEY constraint of car_model enforces a single entry per model. And the FOREIGN KEY constraint (with short notation REFERENCES ...) enforces the same combination of model and brand every time.

To get your original representation, you might add a VIEW like:

CREATE VIEW AS
SELECT c.car_id, c.model, m.brand, c.price
FROM   car c
JOIN   car_model m USING (model);

You might do more. Like collect brands in a separate table ...