Problem
The table below is what I have:
tickername | tickerbbname | tickertype
------------+---------------+------------
USDZAR | USDZAR Curncy | C
EURCZK | EURCZK Curncy | C
EURPLN | EURPLN Curncy | C
USDBRL | USDBRL Curncy | C
USDTRY | USDTRY Curncy | C
EURHUF | EURHUF Curncy | C
USDRUB | USDRUB Curncy | C
I don’t want any given tickername/tickerbbname pair to have more than one column. I’ve already constructed the table and filled it with information (which I have already ensured meets the unique criteria). However, as it grows larger, there is more space for error.
Is it possible to include a UNIQUE constraint at this point?
Asked by Thomas Browne
Solution #1
psql’s inline help:
\h ALTER TABLE
The postgres docs also include information on this (an excellent resource, plus easy to read, too).
ALTER TABLE tablename ADD CONSTRAINT constraintname UNIQUE (columns);
Answered by hhaamu
Solution #2
Yes, you certainly can. However, if your table contains non-unique entries, it will fail. Here’s how to make a table with a unique constraint. You can use the instructions below if you’re running PostgreSQL 9.x.
CREATE UNIQUE INDEX constraint_name ON table_name (columns);
Answered by Zeck
Solution #3
If you wished to add another unique constraint to a table with existing constraints based on, say, name and lastname, you had to drop the entire constraint by:
ALTER TABLE your_table DROP CONSTRAINT constraint_name;
Make sure the new constraint you wish to add is unique/not null across all data in that table (if it’s Microsoft SQL, it can only include one null value), and then re-create it.
ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE (column1, column2, ... column_n);
Answered by Lucas Campos
Solution #4
Yes, a UNIQUE constraint can be added after the fact. However, if your table contains non-unique entries, Postgres will complain about it until you fix it.
Answered by Jordan S. Jones
Post is based on https://stackoverflow.com/questions/1194438/can-i-add-a-unique-constraint-to-a-postgresql-table-after-its-already-created