在 Postgres，如何限制特定列的可能值？

我想在一个表中创建一个列 element_type(称为 discussion) ，它允许文本值“ lessons”或“ quiz”，但是如果将任何其他值插入到该列中，则会生成错误。

我知道我可以创建一个单独的表 element_types，其中包含 element_id(主键，int)和 element_type(惟一的，text)列，并在表 discussion中创建一个外键 foreign_element_id，引用 element_types的 element_id列。或者，我可以完全忘记 element_id，只设置 element_type为主键。但我希望避免创建新表。

是否有更直接的方法来限制列中可能的值而不创建新表？

46112 次浏览
小开
最佳答案

You could add a 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:

CREATE TYPE element_type AS ENUM ('lesson', 'quiz');
小开

This trigger throws an exception whenever someone try to insert or update a row with an invalid 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.

小开

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


提交答案