在PostgreSQL中,基于另一列的值,可以对一部分数值进行约束。

huangapple go评论54阅读模式
英文:

Constraint based on a subset of values depending on the value of another column in PostgreSQL

问题

在PostgreSQL中,我有一个名为“cars”的表(id,model,color)。
如何根据车型限制输入的颜色?
例如:

  • 如果是AA型号,则颜色必须是蓝色、红色或黑色。
  • 如果是BB型号,则颜色必须是蓝色、灰色或黑色。

与在约束条件中硬编码颜色不同,是否可以依赖于基于车型筛选的全局颜色列表?
谢谢你的帮助。

英文:

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

答案1

得分: 1

现在你只能拥有在model_colors表中存在的车型/颜色组合。

英文:

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'), ...

In your cars table:

CREATE TABLE cars
( ...
, model TEXT NOT NULL
, color TEXT NOT NULL
, ...
, FOREIGN KEY (model, color) REFERENCES model_colors (model, color)
);

Now you can only have cars where the model/color exists in model_colors.

答案2

得分: 0

只需创建一个表格来记录它

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')
;
英文:

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')
;

huangapple
  • 本文由 发表于 2023年5月17日 16:49:37
  • 转载请务必保留本文链接:https://go.coder-hub.com/76270196.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定