英文:
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')
;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论