Re: [GENERAL] Best way to alter a foreign constraint - Mailing list pgsql-general
On Mon, Mar 20, 2017 at 11:07 AM, Sylvain MARECHAL <marechal.sylvain2@gmail.com> wrote:
Le 19/03/2017 à 09:54, Sylvain Marechal a écrit :2017-03-18 20:40 GMT+01:00 Adrian Klaver <adrian.klaver@aklaver.com>:In fact, CASCADE is not enough, because I don't know how the test2_t1_fkey is built : does it use the test1_pkey primary key or the test1_t1_key unique key?Why not CASCADE?:On 03/18/2017 12:05 PM, Sylvain Marechal wrote:Hello all,
Some of my tables were badly designed and have 2 indexes, like the
following example (lots of tables have same problem):
<<<
postgres=# \d test1
Table "public.test1"
Column | Type | Modifiers
--------+---------+-----------
t1 | integer | not null
Indexes:
"test1_pkey" PRIMARY KEY, btree (t1)
"test1_t1_key" UNIQUE CONSTRAINT, btree (t1)
Referenced by:
TABLE "test2" CONSTRAINT "test2_t1_fkey" FOREIGN KEY (t1) REFERENCES
test1(t1)
postgres=# \d test2
Table "public.test2"
Column | Type | Modifiers
--------+---------+-----------
t2 | integer | not null
t1 | integer |
Indexes:
"test2_pkey" PRIMARY KEY, btree (t2)
Foreign-key constraints:
"test2_t1_fkey" FOREIGN KEY (t1) REFERENCES test1(t1)
It is not possible to remove the "test1_t1_key" constraint because the
"test2_t1_fkey" internally references it:
<<<
postgres=# ALTER TABLE test1 DROP CONSTRAINT test1_t1_key;
ERROR: cannot drop constraint test1_t1_key on table test1 because other
objects depend on it
DETAIL: constraint test2_t1_fkey on table test2 depends on index
test1_t1_key
HINT: Use DROP ... CASCADE to drop the dependent objects too.
I am sure this information can be found in system catalogs, but I find it safer to explicitely delete then recreate the foreign constraint.
Sylvain
This query might help you determine which key is referenced.
The key information is confkey, which is reported as key position in the referenced table.
Refer to system catalogs description in documentaion for more info. SELECT cn.conname,
CASE WHEN cn.contype = 'c' THEN 'check'
WHEN cn.contype = 'f' THEN 'foreign key'
WHEN cn.contype = 'p' THEN 'primary key'
WHEN cn.contype = 'u' THEN 'unique'
WHEN cn.contype = 't' THEN 'trigger'
WHEN cn.contype = 'x' THEN 'exclusion'
END as type,
cn.condeferrable,
CASE WHEN cn.conrelid > 0
THEN (SELECT nspname || '.' || relname
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.oid = cn.conrelid)
ELSE ''
END as table,
confkey,
consrc
FROM pg_constraint cn
ORDER BY 1;
--
Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
pgsql-general by date: