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

Question

我想在一个名为“discussion”的表中创建一个名为“element_type”的列，允许文字值“lesson”或“quiz”，但如果插入该列的任何其他值将生成错误。

我了解到可以创建一个名为“element_types”的单独表，其中包含列“element_id”（主键，int）和“element_type”（唯一，文本），并在表“discussion”中创建一个外键“foreign_element_id”，引用“element_types”的列“element_id”。或者可以直接把“element_type”设置为主键，而不使用“element_id”。但是我希望避免创建新表。

有没有更直接的方法可以限制列中可能的值，而不创建新的表？

- Deets McGeets

---

为什么要避免创建表？它们并不特别昂贵。 - SingleNegationElimination

重点不在于桌子的成本，而是每当我想要限制可能的值时，不必要的杂物经常会出现。 - Deets McGeets

Answer 1

你可以添加一个 CHECK CONSTRAINT：。

ALTER TABLE distributors 
   ADD CONSTRAINT check_types 
   CHECK (element_type = 'lesson' OR element_type = 'quiz');

尽管我认为更为清晰的选择是创建一个ENUM：

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

- NullUserException

---

非常好。谢谢。对于那些像我一样刚接触Postgres的人，需要注意的是，CREATE TYPE创建了一个用户定义类型，可以像int或text等其他类型一样使用。在这种情况下，可以这样说：CREATE TYPE element_type as ENUM ('lesson', 'quiz'); CREATE TABLE discussion ( type_of_element element_type; ); - Deets McGeets

值得注意的是，虽然最近的版本可以向 ENUM 类型添加值，但没有优雅/简单的方法可以将它们移除。如果您预计允许的值会经常更改，我建议使用检查约束。 - Darren

我们在将postgresql枚举映射到Java枚举时遇到了问题，决定采用“check”继续前进。 - Sergiy Dakhniy

Answer 2

一个快捷语法是:

ALTER TABLE distributors  
   ADD CONSTRAINT check_types 
   CHECK (element_type IN ('lesson', 'quiz') );

这会自动翻译成：

CONSTRAINT check_types CHECK (element_type::text = ANY (ARRAY['lesson'::character varying, 'quiz'::character varying) )

享受吧 ;-)

- ffrey

Answer 3

每当有人尝试插入或更新具有无效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();

如果您想删除硬编码的类型，可以将其适应为检查新类型是否存在于类型表中。

- escrubalo

---

相比于检查约束或者ENUM，这种解决方案非常缓慢且复杂。 - Frank Heikens