Обсуждение: Drop constraint in PostgreSQL 7.1.2

On Mon, 3 Feb 2003, Vilson farias wrote:

> How can I drop a referencial integrity constraint in 7.1.2 once there
> are no such thing before 7.2 release? Can I just execute DROP TRIGGERs
> to get constraints dropped?

Yes, although you have to remember to drop all three triggers for the
constraint.

  Ok, but I'm having some problems to identify wich triggers should be
destroyed. How can I remove those triggers? Can I just delete these records
from pg_trigger?


Check it ou please :

ALTER TABLE cham_atendimento
       ADD CONSTRAINT RLoginXAtend FOREIGN KEY (pa, dt_login)
       REFERENCES pa__login;

bxs=# select * from pg_trigger where tgconstrname = 'rloginxatend';
-[ RECORD
1 ]--+----------------------------------------------------------------------
-------------------------------
tgrelid        | 19854
tgname         | RI_ConstraintTrigger_20889
tgfoid         | 1655
tgtype         | 17
tgenabled      | t
tgisconstraint | t
tgconstrname   | rloginxatend
tgconstrrelid  | 20292
tgdeferrable   | f
tginitdeferred | f
tgnargs        | 8
tgattr         |
tgargs         |
rloginxatend\000cham_atendimento\000pa__login\000UNSPECIFIED\000pa\000pa\000
dt_login\000dt_login\000
-[ RECORD
2 ]--+----------------------------------------------------------------------
-------------------------------
tgrelid        | 19854
tgname         | RI_ConstraintTrigger_20887
tgfoid         | 1654
tgtype         | 9
tgenabled      | t
tgisconstraint | t
tgconstrname   | rloginxatend
tgconstrrelid  | 20292
tgdeferrable   | f
tginitdeferred | f
tgnargs        | 8
tgattr         |
tgargs         |
rloginxatend\000cham_atendimento\000pa__login\000UNSPECIFIED\000pa\000pa\000
dt_login\000dt_login\000
-[ RECORD
3 ]--+----------------------------------------------------------------------
-------------------------------
tgrelid        | 20292
tgname         | RI_ConstraintTrigger_20885
tgfoid         | 1644
tgtype         | 21
tgenabled      | t
tgisconstraint | t
tgconstrname   | rloginxatend
tgconstrrelid  | 19854
tgdeferrable   | f
tginitdeferred | f
tgnargs        | 8
tgattr         |
tgargs         |
rloginxatend\000cham_atendimento\000pa__login\000UNSPECIFIED\000pa\000pa\000
dt_login\000dt_login\000

bxs=# drop trigger RI_ConstraintTrigger_20885 on cham_atendimento;
ERROR:  DropTrigger: there is no trigger ri_constrainttrigger_20885 on
relation cham_atendimento
bxs=# drop trigger RI_ConstraintTrigger_20885 on pa__login;
ERROR:  DropTrigger: there is no trigger ri_constrainttrigger_20885 on
relation pa__login

How can I remove those triggers? Can I just delete these records from
pg_trigger?

Thanks in advance.

----- Original Message -----
From: "Stephan Szabo" <sszabo@megazone23.bigpanda.com>
To: "Vilson farias" <vilson.farias@digitro.com.br>
Cc: <pgsql-general@postgresql.org>; "SIMONE Carla MOSENA"
<simone.mosena@digitro.com.br>
Sent: Monday, February 03, 2003 2:55 PM
Subject: Re: [GENERAL] Drop constraint in PostgreSQL 7.1.2


>
> On Mon, 3 Feb 2003, Vilson farias wrote:
>
> > How can I drop a referencial integrity constraint in 7.1.2 once there
> > are no such thing before 7.2 release? Can I just execute DROP TRIGGERs
> > to get constraints dropped?
>
> Yes, although you have to remember to drop all three triggers for the
> constraint.
>

On Tue, 4 Feb 2003, Vilson farias wrote:

>
>   Ok, but I'm having some problems to identify wich triggers should be
> destroyed. How can I remove those triggers? Can I just delete these records
> from pg_trigger?

>
> ALTER TABLE cham_atendimento
>        ADD CONSTRAINT RLoginXAtend FOREIGN KEY (pa, dt_login)
>        REFERENCES pa__login;
>
> bxs=# select * from pg_trigger where tgconstrname = 'rloginxatend';

 - These three constraints are the ones you need

> bxs=# drop trigger RI_ConstraintTrigger_20885 on cham_atendimento;
> ERROR:  DropTrigger: there is no trigger ri_constrainttrigger_20885 on
> relation cham_atendimento
> bxs=# drop trigger RI_ConstraintTrigger_20885 on pa__login;
> ERROR:  DropTrigger: there is no trigger ri_constrainttrigger_20885 on
> relation pa__login
>
> How can I remove those triggers? Can I just delete these records from

You'll need to double quote the names because they're mixed case.

> pg_trigger?

Not without also altering reltriggers on pg_class.