SQL 在 Postgres 中，如何限制特定列的可能值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7250939/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
In Postgres, how do you restrict possible values for a particular column?
提问by Deets McGeets
I want to create a column element_typein 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_type在表（称为discussion）中创建一个列，该列允许文本值“课程”或“测验”，但如果将任何其他值插入该列，则会生成错误。
I understand that I could create a separate table called element_typeswith columns element_id(primary key, int) and element_type(unique, text) and create a foreign key foreign_element_idin the table discussionreferencing element_types's column element_id. Or alternatively, I could forget element_idaltogether and just set element_typeas the primary key. But I want to avoid creating a new table.
我知道我可以创建一个单独的表，称为element_types列element_id(primary key, int) 和element_type(unique, text) 并foreign_element_id在表discussion引用element_types的 column 中创建外键element_id。或者，我可以element_id完全忘记并设置element_type为主键。但我想避免创建一个新表。
Is there a more straightforward way to restrict possible values in a column without creating a new table?
有没有更直接的方法来限制列中的可能值而不创建新表？
回答by NullUserException
You could add a CHECK CONSTRAINT:
你可以添加一个 CHECK CONSTRAINT:
ALTER TABLE distributors
ADD CONSTRAINT check_types
CHECK (element_type = 'lesson' OR element_type = 'quiz');
Although IMO the cleaner option would be to create an ENUM:
尽管 IMO 更清洁的选择是创建一个ENUM：
CREATE TYPE element_type AS ENUM ('lesson', 'quiz');
回答by ffrey
A shorcut syntax is :
快捷语法是：
ALTER TABLE distributors
ADD CONSTRAINT check_types
CHECK (element_type IN ('lesson', 'quiz') );
This translates automaticolly to :
这自动转换为：
CONSTRAINT check_types CHECK (element_type::text = ANY (ARRAY['lesson'::character varying, 'quiz'::character varying) )
Enjoy ;-)
享受 ;-）
回答by escrubalo
This trigger throws an exception whenever someone try to insert or update a row with an invalid element_type.
每当有人尝试插入或更新具有无效 element_type 的行时，此触发器都会引发异常。
CREATE OR REPLACE FUNCTION check_discussion_element_type() RETURNS TRIGGER AS $$
DECLARE new_element_type varchar(25);
BEGIN
SELECT element_type into new_element_type
FROM discussion
WHERE discussion.element_id = NEW.element_id;
IF new_element_type != 'lesson' AND new_element_type != 'quiz'
THEN RAISE EXCEPTION 'Unexpected discussion type';
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
create trigger t_check_discussion_element_type after update or insert on discussion for each row execute procedure check_discussion_element_type();
If you want to remove the hard-coded types you can adapt it to check if the new type exists in a type table.
如果要删除硬编码类型，可以对其进行调整以检查新类型是否存在于类型表中。
