Deets McGeets Deets McGeets - 4 months ago 23
SQL Question

In Postgres, how do you restrict possible values for a particular column?

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.

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?

Answer

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