I have the following table:
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 there to ever be more than one column for any given tickername
/tickerbbname
pair. I've already created the table and have lots of data in it (which I have already ensured meets the unique criteria). As it gets larger, though, room for error creeps in.
Is there any way to add a UNIQUE
constraint at this point?
This question is related to
sql
database
postgresql
indexing
unique-constraint
If you had a table that already had a existing constraints based on lets say: name and lastname and you wanted to add one more unique constraint, you had to drop the entire constrain by:
ALTER TABLE your_table DROP CONSTRAINT constraint_name;
Make sure tha the new constraint you wanted to add is unique/ not null ( if its Microsoft Sql, it can contain only one null value) across all data on that table, and then you could re-create it.
ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE (column1, column2, ... column_n);
Yes, you can add a UNIQUE constraint after the fact. However, if you have non-unique entries in your table Postgres will complain about it until you correct them.
Yes, you can. But if you have non-unique entries on your table, it will fail. Here is the how to add unique constraint on your table. If you're using PostgreSQL 9.x you can follow below instruction.
CREATE UNIQUE INDEX constraint_name ON table_name (columns);
Source: Stackoverflow.com