Hi, The issue is due to records in Account_message is still exists for the records which are going to be deleted from the Message table. Please check the sequence of deleting the records.
When I tried to delete a record using your example, the following exception is raised. ERROR: update or delete on "message" violates foreign key constraint "account_message__msg_fkey" on "account_message" DETAIL: Key (_message_id)=(2) is still referenced from table "account_message". Regards, R.Muralidharan -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Sonic Sent: Wednesday, May 04, 2005 1:01 PM To: pgsql-sql@postgresql.org Subject: [SQL] CASCADE and TRIGGER - Some weird problem -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 hi, have a little problem with a trigger and the ON DELETE CASCADE statement. i'm working on a db that represents Users and Messages. A message can be owned by more than a user. if i delete a user all his related objects are deleted too (ON DELETE CASCADE), but if a message that this user owns is also owned by another user, it has not to be deleted. i just put ON DELETE CASCADE statement on foreign keys and thos seem to work. then i wrote a trigger to check if each message the user owns is owned by someone else. if it's not delete it! - ----------------------------------------------------------- - -----this is the code: CREATE TABLE OWNER( _LOGIN TEXT, PRIMARY KEY(_LOGIN) ); CREATE TABLE MESSAGE( _MESSAGE_ID TEXT, PRIMARY KEY(_MESSAGE_ID) ); CREATE TABLE ACCOUNT( _INDIRIZZO TEXT UNIQUE, _LOGIN TEXT, PRIMARY KEY(_INDIRIZZO,_LOGIN), FOREIGN KEY(_LOGIN)REFERENCES OWNER(_LOGIN) ON DELETE CASCADE); CREATE TABLE ACCOUNT_MESSAGE( _MSG TEXT, _INDIRIZZO TEXT, PRIMARY KEY(_MSG,_INDIRIZZO), FOREIGN KEY(_MSG)REFERENCES MESSAGE(_MESSAGE_ID), FOREIGN KEY(_INDIRIZZO)REFERENCES ACCOUNT(_INDIRIZZO) ON DELETE CASCADE); CREATE TABLE FOLDER( _PATH TEXT, _OWNER TEXT, PRIMARY KEY(_PATH), FOREIGN KEY(_OWNER)REFERENCES OWNER(_LOGIN) ON DELETE CASCADE); CREATE TABLE MSG_IN_FOLDER( _MSG TEXT, _FOLDER TEXT, PRIMARY KEY(_MSG,_FOLDER), FOREIGN KEY(_MSG)REFERENCES MESSAGE(_MESSAGE_ID), FOREIGN KEY(_FOLDER)REFERENCES FOLDER(_PATH) ON DELETE CASCADE); CREATE FUNCTION check_message() RETURNS trigger AS $check_MESSAGE$ BEGIN DELETE FROM MESSAGE WHERE _message_id IN ( SELECT _MSG FROM ACCOUNT_MESSAGE NATURAL JOIN msg_in_FOLDER WHERE _MSG = OLD._MSG GROUP BY _MSG HAVING count(*)=1 ); RAISE NOTICE 'Value of OLD._MSG %', OLD._MSG; RETURN NULL; END; $check_MESSAGE$ LANGUAGE plpgsql; CREATE TRIGGER check_message AFTER DELETE ON MSG_IN_FOLDER FOR EACH ROW EXECUTE PROCEDURE check_message(); - ------------------------------------------------- - --and these are some values: delete from OWNER; delete from ACCOUNT; delete from MESSAGE; delete from ACCOUNT_MESSAGE; delete from FOLDER; delete from MSG_IN_FOLDER; insert into OWNER (_login) values ('anna'); insert into OWNER (_login) values ('paolo'); insert into ACCOUNT values ('[EMAIL PROTECTED]', 'anna'); insert into ACCOUNT values ('[EMAIL PROTECTED]', 'paolo'); insert into MESSAGE (_message_id) values ('1'); insert into MESSAGE (_message_id) values ('2'); insert into ACCOUNT_MESSAGE values ('1', '[EMAIL PROTECTED]'); insert into ACCOUNT_MESSAGE values ('1', '[EMAIL PROTECTED]'); insert into ACCOUNT_MESSAGE values ('2', '[EMAIL PROTECTED]'); insert into FOLDER (_path, _OWNER) values ('c', 'anna'); insert into MSG_IN_FOLDER values ('1', 'c'); insert into MSG_IN_FOLDER values ('2', 'c'); select * from MESSAGE; - ---------- as you see there are 2 messages. message 1 is owned both by 'anna' and 'paolo'. message 2 is owned just by 'anna'. now what i want is that if i delete user 'anna' just message 2 is deleted. i guess i've done that with my trigger: ... CREATE FUNCTION check_message() RETURNS trigger AS $check_MESSAGE$ BEGIN DELETE FROM MESSAGE WHERE _message_id IN ( SELECT _MSG FROM ACCOUNT_MESSAGE NATURAL JOIN msg_in_FOLDER WHERE _MSG = OLD._MSG GROUP BY _MSG HAVING count(*)=1 ); RAISE NOTICE 'Value of OLD._MSG %', OLD._MSG; RETURN NULL; END; $check_MESSAGE$ LANGUAGE plpgsql; CREATE TRIGGER check_message AFTER DELETE ON MSG_IN_FOLDER FOR EACH ROW EXECUTE PROCEDURE check_message(); ... i'm also sure that values it process are right beacuse of that RAISE NOTICE 'Value of OLD._MSG %', OLD._MSG; but something seems to go wrong. all 'anna' stuff is deleted but not her message 2 ! DELETE FROM owner WHERE _login = 'anna'; SELECT * FROM message; can someone help me to understand why this is not done? i don't receive any error message from postgres... thanks paolo from italy [EMAIL PROTECTED] -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.0 (MingW32) iD8DBQFCeHpD8gTT7JZTWqIRAvc6AJ9yqXq4EOP+JZ4NJ+pekiwuqko0XACeMz/8 DBZdQevWM1emBodYH5QP0G4= =V6VW -----END PGP SIGNATURE----- ---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
