From: | Andre Schlieper <Andre(dot)Schlieper(at)gmx(dot)de> |
---|---|
To: | pgsql-admin(at)postgresql(dot)org |
Subject: | problems removing foreign-key triggers, postgresql 7.1.1 |
Date: | 2002-07-14 18:57:29 |
Message-ID: | 3D31C999.99790392@gmx.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
Hi there,
I've some problems droping tiggers (foreign key-constraint) on
postgresql 7.1.1,
example-tables are like this :
CREATE TABLE pers (
pid INT NOT NULL PRIMARY KEY,
pname TEXT NOT NULL);
CREATE TABLE tasks (
taskid SERIAL NOT NULL PRIMARY KEY,
pid INT NOT NULL CONSTRAINT tasks__ref_p REFERENCES
pers,
task TEXT NOT NULL);
This creates two simple tables, linked with one foreign-key
"tasks_ref_p".
This constraint is done with three triggers (2 on the pk table, 1 on the
fk table), wich are named:
RI_ConstraintTrigger_594265
RI_ConstraintTrigger_594267
RI_ConstraintTrigger_594263
I tried to delete one of these triggers with
drop trigger "RI_ConstraintTrigger_594265" on pers;
that fails with
ERROR: DropTrigger: there is no trigger ri_constrainttrigger_594265 on
relation pers
I also tried to delete these triggers with
DELETE FROM pg_trigger WHERE tgname='RI_ConstraintTrigger_594265';
wich worked, all trigger are away from pg_trigger.
But now if i insert one in tasks with
INSERT INTO tasks (pid, task) VALUES (3, 'Install Linux');
still fails with :
ERROR: tasks__ref_p referential integrity violation - key referenced
from tasks not found in pers
so the foreign key is not away ?!
After restart of psql there's another message if i insert with
INSERT INTO tasks (pid, task) VALUES (3, 'Install Linux');
ERROR: RelationBuildTriggers: 1 record(s) not found for rel tasks
Then I tried to fix pg_class(reltriggers) with
update pg_class set reltriggers=0 where relname='tasks';
update pg_class set reltriggers=0 where relname='pers';
And did
INSERT INTO tasks (pid, task) VALUES (3, 'Install Linux');
wich finaly worked, but I don't know why.
Q1 : How can I get DROP TRIGGER to work, why does it not work here ?
Q2: what's the right strategy to delete directly on pg_trigger ? was it
only luck that it is working that way; deleting on pg_triggers and
fixing on pg_class(reltriggers) ?
Thanx! Andre
From | Date | Subject | |
---|---|---|---|
Next Message | ventas | 2002-07-14 20:53:55 | |
Previous Message | Jefim Matskin | 2002-07-14 09:50:57 | Multilingual issues |