Remove Uniqueness of Index in Postgresql

Remove uniqueness of index in PostgreSQL

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

How to drop a unique constraint on a column in Postgres?

To find the name of the unique constraint, run

SELECT conname
FROM pg_constraint
WHERE conrelid = 'cart'::regclass
  AND contype = 'u';

Then drop the constraint as follows:

ALTER TABLE cart DROP CONSTRAINT cart_shop_user_id_key;

Replace cart_shop_user_id_key with whatever you got from the first query.

How can I delete unique constraints of thousands of indices in psql?

You can do it in a single query.

The idea is to identify the constraints to be removed, to build a list of command to remove them and to execute them. For this, you can use psql /gexec functionality.

 SELECT format('alter table %I drop constraint %I;', rel.relname, conname)
FROM pg_catalog.pg_constraint con
    INNER JOIN pg_catalog.pg_class rel
      ON rel.oid = con.conrelid
    INNER JOIN pg_catalog.pg_namespace nsp
      ON nsp.oid = connamespace
WHERE nsp.nspname = 'public'
  AND rel.relname = 'users'
  AND conname ilike  'users_email_key%';\gexec       

PS: there is no undo... you may want to print the commands (i.e. run without the \gexec flag) first.

Drop unique index when having Primary Key index

Thanks to the discussion in comments (@Gordon Linoff) here's a way to fix this issue:

• Drop foreign keys
• Drop Primary Keys and Unique Keys
• Recreate Primary Key only
• Recreate Foreign Keys

ALTER TABLE steps_routes DROP CONSTRAINT steps_routes_step_hash_id_fkey;
ALTER TABLE steps_likes_dislikes DROP CONSTRAINT steps_likes_dislikes_step_hash_id_fkey;
ALTER TABLE steps DROP CONSTRAINT steps_pkey;
ALTER TABLE steps DROP CONSTRAINT steps_hash_id_key;
ALTER TABLE steps ADD PRIMARY KEY (hash_id);
ALTER TABLE steps_routes ADD FOREIGN KEY (step_hash_id) REFERENCES steps(hash_id) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE steps_likes_dislikes ADD FOREIGN KEY (step_hash_id) REFERENCES steps(hash_id) ON DELETE CASCADE ON UPDATE CASCADE;

---

Related Topics