В Postgres, как вы ограничиваете возможные значения для определенного столбца?

Я хочу создать столбец element_type в таблице (называемый discussion), который позволяет текстовым значениям "урок" или "опрос", но будет генерировать ошибку, если любое другое значение будет вставлено в этот столбец.

Я понимаю, что я мог бы создать отдельную таблицу с именем element_types с столбцами element_id (первичный ключ, int) и element_type (уникальный, текст) и создать внешний ключ foreign_element_id в таблице discussion ссылаясь на столбец element_types element_id. Или, альтернативно, я мог вообще забыть element_id и просто установить element_type в качестве первичного ключа. Но я хочу избежать создания новой таблицы.

Есть ли более простой способ ограничить возможные значения в столбце без создания новой таблицы?

Ответ 1

Вы можете добавить CHECK CONSTRAINT:

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

Несмотря на то, что IMO более чистым вариантом было бы создать ENUM:

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

Ответ 2

Синтаксис shorcut:

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

Наслаждайтесь; -)

Ответ 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();

Если вы хотите удалить жестко запрограммированные типы, вы можете настроить его, чтобы проверить, существует ли новый тип в таблице типов.