在Postgres中,如何限制特定列的可能值? [英] In Postgres, how do you restrict possible values for a particular column?
问题描述
我想在表中创建列 element_type
(称为 discussion
),该列允许文本值课程或测验,但如果在该列中插入任何其他值,则会产生错误。
I want to create a column element_type
in a table (called discussion
) that allows the text values "lesson" or "quiz" but will generate an error if any other value is inserted into that column.
我知道我可以创建一个单独的表,称为 element_types
,其中列 element_id
(主键,整数)和 element_type
(唯一,文本) ),并在引用 element_types $ c $的表
discussion
中创建外键 foreign_element_id
c>的列 element_id
。或者,我可能会完全忘记 element_id
,而只是将 element_type
设置为主键。但是我想避免创建新表。
I understand that I could create a separate table called element_types
with columns element_id
(primary key, int) and element_type
(unique, text) and create a foreign key foreign_element_id
in the table discussion
referencing element_types
's column element_id
. Or alternatively, I could forget element_id
altogether and just set element_type
as the primary key. But I want to avoid creating a new table.
是否有更直接的方法来限制列中可能的值而不创建新表?
Is there a more straightforward way to restrict possible values in a column without creating a new table?
推荐答案
您可以添加 检查约束:
You could add a CHECK CONSTRAINT:
ALTER TABLE distributors
ADD CONSTRAINT check_types
CHECK (element_type = 'lesson' OR element_type = 'quiz');
尽管IMO较干净的选择是创建 ENUM
:
Although IMO the cleaner option would be to create an ENUM
:
CREATE TYPE element_type AS ENUM ('lesson', 'quiz');
这篇关于在Postgres中,如何限制特定列的可能值?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!