Thread: A Referntial integrity

Actually, you should (hopefully) be able to drop
t2.  That is supposed to remove the constraints
referencing it (technically speaking drop table is
probably supposed to take an argument to
determine whether to do this, but we don't yet).

If it's actually failing for you, can you give us a
copy of the error message you're getting?

You will need to recreate the constraint between
t1 and t2 when you re-add t2 using alter table
add constraint.

----- Original Message -----
From: Niraj Bhatt
To: pgsql-general@postgresql.org
Cc: balpannu1@aol.com
Sent: Thursday, July 06, 2000 7:29 AM
Subject: [GENERAL] A Referntial integrity


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 table
since it has got child in table t1.
Do I need to drop t1 as well ?? This one is a classical example of master
detail - detail relation ship with dependent details which is very trivial
in real world. Infect in more complex design such detail - detail
relationship 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

> 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.