Hi,
In my database there is a table "request" which is referenced
by a couple of other tables (about 4 or 5). Since quite frequently
I need to delete "request"s with any related data, I formulate
the foreign keys in the following way:
request_id INTEGER
CONSTRAINT n_request_id NOT NULL
CONSTRAINT f_request_id
REFERENCES request ON DELETE CASCADE
DEFERRABLE INITIALLY DEFERRED,
Here comes the definition of "request":
CREATE TABLE request (
request_id INTEGER
CONSTRAINT d_request_id DEFAULT nextval('s_request_id'::text)
CONSTRAINT p_request_id PRIMARY KEY,
label TEXT CONSTRAINT n_label NOT NULL,
description TEXT ,
announce_date DATE CONSTRAINT d_announce_date DEFAULT CURRENT_DATE,
submit_date DATE CONSTRAINT n_submit_date NOT NULL,
mail_subject TEXT ,
mail_message TEXT ,
organization_id INTEGER
CONSTRAINT n_organization_id NOT NULL
CONSTRAINT f_organization_id REFERENCES organization,
questionnare_id INTEGER
CONSTRAINT n_questionnare_id NOT NULL
CONSTRAINT f_questionnare_id REFERENCES questionnare,
status "char"
CONSTRAINT x_status
CHECK (upper(status) IN ('S','A','O','C','E')),
-- [S]aved, to-[A]nnounce, [O]pen = announced, [C]losed, [E]rror
participated INTEGER CONSTRAINT d_paricipated DEFAULT 0,
asked INTEGER ,
CONSTRAINT x_request_dates CHECK (submit_date > announce_date)
)
WITHOUT OIDS;
and when I execute the query "DELETE FROM request WHERE ..." I got
the following error message:
ERROR: SPI_execp() failed in RI_FKey_cascade_del()
My questions:
- Is it connected to permission somehow?
(delete revoked some of the related tables)
- What to do to get work the cascaded delete?
(with version 7.2.1 of postgres as a database superuser (postgres))
Gyozo Papp
- pgerzson@freestart.hu