Friday, January 2, 2009

Postgress Error

I got the following error in when i tried to delete a record from a table "acc_contract"

"ERROR: update or delete on "acc_cost" violates foreign key constraint "acc_room2acc_cost" on "acc_room"
DETAIL: Key (contract_id,cost_no)=(1000,2) is still referenced from table "acc_room".
CONTEXT: SQL statement "DELETE FROM ONLY "public"."acc_cost" W "

I am using EMS to access the database, but it does not show any foreign keys in the table "acc_cost"

Then i accessed directly to DB using putty (an interface which helps you to access and run linux commands and so on) and got the description of the table "acc_cost"
it gave me the follwing out put

------------------------------------------

\d acc_cost
Table "public.acc_cost"
Column | Type | Modifiers
-------------+---------+------------------------
contract_id | bigint | not null
cost_no | integer | not null
basic | boolean | not null default false

Indexes:
"pk_acc_cost" PRIMARY KEY, btree (contract_id, cost_no)

Triggers:
"RI_ConstraintTrigger_982292245" AFTER DELETE ON acc_cost FROM acc_room NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_restrict_del"('acc_room2acc_cost', 'acc_room', 'acc_cost', 'UNSPECIFIED', 'contract_id', 'contract_id', 'cost_no', 'cost_no')
"RI_ConstraintTrigger_982292246" AFTER UPDATE ON acc_cost FROM acc_room NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_cascade_upd"('acc_room2acc_cost', 'acc_room', 'acc_cost', 'UNSPECIFIED', 'contract_id', 'contract_id', 'cost_no', 'cost_no')
"RI_ConstraintTrigger_982292715" AFTER INSERT OR UPDATE ON acc_cost FROM acc_contract NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_check_ins"('acc_cost2acc_contract', 'acc_cost', 'acc_contract', 'UNSPECIFIED', 'contract_id', 'contract_id')


-------------------------------------------------------

So u can see the RI constraints triggers in the table.

the problem comes because

---------------------------------------------------------

Triggers:

"RI_ConstraintTrigger_982292245" AFTER DELETE ON acc_cost FROM acc_room NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_restrict_del"('acc_room2acc_cost', 'acc_room', 'acc_cost', 'UNSPECIFIED', 'contract_id', 'contract_id', 'cost_no', 'cost_no')

------------------

So i dropped this trigger

DROP TRIGGER "RI_ConstraintTrigger_982292245" ON acc_cost;

Important
RI_ConstraintTrigger_982292245
should be within the quotations cause its name is in different case and postgres is case sensitive
after that i could delete records

No comments :