Re: [GENERAL] Foreign key references a unique index instead of a primary key

[Arjen Nienhuis]

On Feb 23, 2017 12:42 PM, "Ivan Voras"  wrote:

Hello,

I've inherited a situation where:

   - a table has both a primary key and a unique index on the same field.
   - at some time, a foreign key was added which references this table
   (actually, I'm not sure about the sequence of events), which has ended up
   referencing the unique index instead of the primary key.

Now, when I've tried dropping the unique index, I get an error that the
foreign key references this index (with a hint I use DROP...CASCADE).

This drop index is a part of an automated plpgsql script which deletes
duplicate indexes, so I'm interested in two things:

   1. How to detect if a foreign key depends on an index I'm about to drop,
   so I can skip it
   2. Is there a way to get around this situation, maybe modify the
   pg_constraint table or other tables to reference the index / primary key I
   want

You could recreate the primary key USING the unique index. This can be done
in a transaction without scanning the table. That way there's only one
index left.

ALTER TABLE my_table
ADD CONSTRAINT PK_my_table PRIMARY KEY USING INDEX my_index;


http://dba.stackexchange.com/questions/8814/how-to-promote-an-existing-index-to-primary-key-in-postgresql

Re: [GENERAL] Foreign key references a unique index instead of a primary key

[Adrian Klaver]

On 02/23/2017 03:40 AM, Ivan Voras wrote:
> Hello,
> 
> I've inherited a situation where:
> 
>   * a table has both a primary key and a unique index on the same field. 
>   * at some time, a foreign key was added which references this table
> (actually, I'm not sure about the sequence of events), which has
> ended up referencing the unique index instead of the primary key.
> 
> Now, when I've tried dropping the unique index, I get an error that the
> foreign key references this index (with a hint I use DROP...CASCADE).
> 
> This drop index is a part of an automated plpgsql script which deletes
> duplicate indexes, so I'm interested in two things:
> 
>  1. How to detect if a foreign key depends on an index I'm about to
> drop, so I can skip it

Trap the error and move on?:

https://www.postgresql.org/docs/9.6/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING

So something like:

drop index skill_code_u ;
ERROR:  cannot drop index skill_code_u because constraint skill_code_u on table 
skill_codes requires it
HINT:  You can drop constraint skill_code_u on table skill_codes instead.


CREATE OR REPLACE FUNCTION public.exception_test()
 RETURNS void
 LANGUAGE plpgsql
AS $function$
DECLARE
state_text varchar;
BEGIN

DROP INDEX skill_code_u;
EXCEPTION
WHEN OTHERS THEN
GET STACKED DIAGNOSTICS   state_text =  RETURNED_SQLSTATE;
RAISE NOTICE '%', state_text;

END;
$function$

Where OTHERS is a special catchall condition.

select exception_test();
NOTICE:  2BP01
 exception_test 


Looking up 2BP01 here:

https://www.postgresql.org/docs/9.6/static/errcodes-appendix.html

shows that it is:

dependent_objects_still_exist

You could narrow the exception to:

EXCEPTION
WHEN dependent_objects_still_exist THEN


>  2. Is there a way to get around this situation, maybe modify the
> pg_constraint table or other tables to reference the index / primary
> key I want

I don't know if that would be wise, it would seem to skip the step where the FK 
verifies that the column it is pointing at actually has unique values. In 
general
the idea of directly modifying system tables makes me nervous.

> 
> ?
> 
> This is on PostgreSQL 9.3.
> 


-- 
Adrian Klaver
adrian.kla...@aklaver.com


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general