Add Constraint to Table in PostgreSQL
ALTER TABLE ADD CONSTRAINT statement to add constraint to a table.
ALTER TABLE <table_name> ADD CONSTRAINT <constraint_name> <constraint_definition>;
Using ALTER TABLE ADD CONSTRAINT, any of bellow constraints can be added to existing table
- NOT NULL
- PRIMARY KEY
- FOREIGN KEY
Consider that you already have the following
Add UNIQUE Constraint
The UNIQUE constraint ensures that the values of a column must be unique and not a duplicate value.
The following will set the unique constraint on the
ALTER TABLE employee ADD CONSTRAINT employee_unq UNIQUE(email);
The following is the result of the above statement.
Add NOT NULL Constraint
The NOT NULL constraint ensures ensure that a given column of a table is never assigned the null value. To add or remove
NOT NULL constraint to a column of table you need to use ALTER COLUMN clause with ALTER TABLE.
The following will set the
NOT NULL constraint on the
gender column of the
ALTER TABLE employee ALTER COLUMN gender SET NOT NULL;
To remove the
NOT NULL constraint, use the
DROP clause, as shown below.
ALTER TABLE employee ALTER COLUMN gender DROP NOT NULL;
Add Constraints using pgAdmin
You can add constraints using pgAdmin by right clicking on the table and select 'Properties' in the context menu. This will open a popup where you can add or edit multiple columns definition.
In the popup, go to 'Constraints' tab where you can add or edit Primary key, Foreign Key, Check, and unique constraints, as shown below.