> Hello,
>
> We are using a postgresql-7.0.2. Consider the following scenario
>
> we have created a table t1 with columns
>
> c1 : having referential integrity with t2.c1
> c2 : having referential integrity with t3.c2
>
> where t2 and t3 are different tables
>
> Assume that t2 has also got a referential integrity with t4.c3 where c3 is
=
> a column in t2 as well.
>
> Now I want to drop a constraint of table t2 that is referring to t4.c3. As
=
> per the documentation one can not drop a constraint in alter table
command.=
> In this situation I need to drop the table t2. But I can not drop this
tab=
> le since it has got child in table t1.
> Do I need to drop t1 as well ?? This one is a classical example of master
d=
> etail - detail relation ship with dependent details which is very trivial
i=
> n real world. Infect in more complex design such detail - detail
relationsh=
> ip can go upto several levels. Every time dropping a table is not good. Is
=
> there any other way to do this?? Please elaborate on this
>
> Regards
>
> Niraj Bhatt
No, you don't. Referential integrity is maintained by means of triggers in
postgresql, so you can perform query like that:
SELECT t.tgname, c1.relname
FROM pg_trigger t
INNER JOIN pg_class c1 ON t.tgrelid = c1.oid
INNER JOIN pg_class c2 ON t.tgconstrrelid = c2.oid
WHERE
(c1.relname = 't1' AND c2.relname = 't2') OR
(c1.relname = 't2' AND c2.relname = 't1');
, where t1 references t2 (or vice versa), and you will get three rows (or a
multiple of three, if there are more than one reference between these
tables), which will contain the names of referential triggers, like that:
tgname | relname
----------------------------+------------
RI_ConstraintTrigger_22073 | t2
RI_ConstraintTrigger_22075 | t2
RI_ConstraintTrigger_22071 | t1
(3 rows)
Drop these triggers (there is one on the referencing table, and two ones on
the referenced table), and there will be no reference anymore.
Alex Bolenok.
