删除 PostgreSQL 中索引的唯一性

在我的 PostgreSQL 数据库中，我用这种方式创建了一个唯一的索引:

CREATE UNIQUE INDEX <my_index> ON <my_table> USING btree (my_column)

是否有办法更改索引以删除唯一约束？我看了 ALTER 索引文档，但它似乎没有做我需要的。

我知道我可以删除索引并创建另一个，但我想找到一个更好的方法，如果它存在的话。

95232 次浏览
小开

I don't think it's possible... even in the pgAdmin III UI, if you try to edit a constraint created with your statement, the "Unique" box is greyed-out; you can't change it through the UI. Combined with your research on the ALTER INDEX docs, I'd say it can't be done.

小开
最佳答案

You may be able to remove the unique CONSTRAINT, and not the INDEX itself.

Check your CONSTRAINTS via select * from information_schema.table_constraints;

Then if you find one, you should be able to drop it like:

ALTER TABLE <my_table> DROP CONSTRAINT <constraint_name>

Edit: a related issue is described in this question

小开

Assume you have the following:

Indexes:
"feature_pkey" PRIMARY KEY, btree (id, f_id)
"feature_unique" UNIQUE, btree (feature, f_class)
"feature_constraint" UNIQUE CONSTRAINT, btree (feature, f_class)

To drop the UNIQUE CONSTRAINT, you would use ALTER TABLE:

ALTER TABLE feature DROP CONSTRAINT feature_constraint;

To drop the PRIMARY KEY, you would also use ALTER TABLE:

ALTER TABLE feature DROP CONSTRAINT feature_pkey;

To drop the UNIQUE [index], you would use DROP INDEX:

DROP INDEX feature_unique;
小开

Searched for hours for the same quesiton and doesnt seem to get a right answer---- all the given answers just failed to work.

For not null, it also took me some time to find. Apparently for some reason, the majority-certified codes just dont work when I use it.

I got the not null version code, something like this

ALTER TABLE tablename
ALTER COLUMN column_want_to_remove_constriant
DROP NOT NULL

Sadly changing 'not null' to 'unique' doesnt work.

小开

If you have PgAdmin4 installed than it's very easy just follow below steps...

  1. Go to table properties(right click on table and select properties).
  2. A window will open than navigate to Constraints in properties window.
  3. In Constraints option you will see options as below screenshot just go to Unique there you will see columns that has unique constraints. Click on delete button and save. that's it you are good to go. enter image description here
小开

this worked for me, no need to specify table since the index is unique in your case:

DROP INDEX if exists my_index_name;

提交答案