SQL 在 PostgreSQL 表创建后，我可以向它添加 UNIQUE 约束吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1194438/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Can I add a UNIQUE constraint to a PostgreSQL table, after it's already created?
提问by Thomas Browne
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/tickerbbnamepair. 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.
我不希望任何给定tickername/tickerbbname对的列都不止一列。我已经创建了表格并在其中包含了大量数据（我已经确保这些数据符合独特的标准）。然而，随着它变得越来越大，错误的空间就会慢慢出现。
Is there any way to add a UNIQUEconstraint at this point?
有没有办法UNIQUE在这一点上添加约束？
回答by hhaamu
psql's inline help:
psql的内联帮助：
\h ALTER TABLE
Also documented in the postgres docs(an excellent resource, plus easy to read, too).
也记录在postgres 文档中（一个很好的资源，也很容易阅读）。
ALTER TABLE tablename ADD CONSTRAINT constraintname UNIQUE (columns);
回答by Zeck
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.
是的你可以。但是，如果您的表中有非唯一条目，它将失败。这是在表上添加唯一约束的方法。如果您使用的是 PostgreSQL 9.x，则可以按照以下说明进行操作。
CREATE UNIQUE INDEX constraint_name ON table_name (columns);
回答by Lucas Campos
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:
如果您的表已经存在基于以下条件的约束：name 和 lastname，并且您想再添加一个唯一约束，则必须通过以下方式删除整个约束：
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.
确保您要添加的新约束在该表的所有数据中是唯一的/不为空（如果它是 Microsoft Sql，则它只能包含一个空值），然后您可以重新创建它。
ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE (column1, column2, ... column_n);
回答by Jordan S. Jones
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.
是的，您可以事后添加 UNIQUE 约束。但是，如果您的表中有非唯一条目，Postgres 会抱怨它，直到您更正它们。
