Postgres Pro
Downloads
Home   >   mailing lists

Thread: Drop constraint in PostgreSQL 7.1.2

Drop constraint in PostgreSQL 7.1.2

From
"Vilson farias"
Date:
Greetings,
 
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?
 
Thanks in advance.
 
--------------------------------------------------------------------------------
José Vilson de Mello de Farias
Software Engineer
 
Dígitro Tecnologia Ltda - www.digitro.com.br
APC - Customer Oriented Applications
E-mail: vilson.farias@digitro.com.br
Tel.: +55 48 281 7158
ICQ 11866179

Re: Drop constraint in PostgreSQL 7.1.2

From
Stephan Szabo
Date:
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.

Re: Drop constraint in PostgreSQL 7.1.2

From
"Vilson farias"
Date:
  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.
>

Re: Drop constraint in PostgreSQL 7.1.2

From
Stephan Szabo
Date:
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.