Menu
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?
666
The syntax to modify a column in a table in PostgreSQL (using the ALTER TABLE statement) is: ALTER TABLE table_name ALTER COLUMN column_name TYPE column_definition; table_name. The name of the table to modify.
PostgreSQL SELECT statement syntax If you specify a list of columns, you need to place a comma ( , ) between two columns to separate them. If you want to select data from all the columns of the table, you can use an asterisk ( * ) shorthand instead of specifying all the column names.
From postgresql documentation: RESTRICT prevents deletion of a referenced row. NO ACTION means that if any referencing rows still exist when the constraint is checked, an error is raised; this is the default behavior if you do not specify anything.
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'); If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
