general sql error: violation of foreign key constraint

By: Borland Staff

Abstract: he problem occurs because you are using the incorrect type of Trigger

Problem:
Trying to use implement a cascading delete trigger and 
getting a foreign key violation error.  When I pass the following 
SQL:
	
		DELETE "record_x"
		FROM master
		WHERE master_id = "value_x";
		
I get the foreign key violation error.


Solution:
The problem occurs, because you are using the incorrect type of 
trigger.  A BEFORE DELETE trigger must be used instead of an 
AFTER DELETE trigger.

This is an example using an AFTER DELETE trigger.  (causes error)
----------------------------------------------------------------------------------------------
 	
CONNECT "mydb.gdb" user "sysdba" password "masterkey";

/* Domain definitions */
CREATE DOMAIN ID AS INTEGER;

/* Table definitions */
CREATE TABLE MASTER (MASTER_ID ID NOT NULL,
MASTER_DSC CHAR(18),
PRIMARY KEY (MASTER_ID));

CREATE TABLE DETAIL (MASTER_ID ID NOT NULL,
DETAIL_ID ID NOT NULL,
DETAIL_DSC CHAR(18),
PRIMARY KEY (MASTER_ID, DETAIL_ID),
FOREIGN KEY (MASTER_ID) REFERENCES MASTER(MASTER_ID));

COMMIT;

SET TERM ^ ;

/* Trigger */
CREATE TRIGGER DEL_DETAIL.MASTER_ID FOR MASTER                          
ACTIVE AFTER DELETE POSITION 0 
AS
DECLARE VARIABLE numrows INTEGER;
BEGIN
  DELETE FROM Detail
  WHERE Detail.Master_ID = OLD.Master_ID;
END ^

COMMIT WORK ^

SET TERM ; ^


This is an example using the BEFORE DELETE trigger:  (Will Work)
-----------------------------------------------------------------------------
	
SET TERM ^ ;

/* Trigger */
CREATE TRIGGER DEL_DETAIL.MASTER_ID FOR MASTER                          
ACTIVE BEFORE DELETE POSITION 0 
AS
DECLARE VARIABLE numrows INTEGER;
BEGIN
  DELETE FROM Detail
  WHERE Detail.Master_ID = OLD.Master_ID;
END ^

COMMIT WORK ^

SET TERM ; ^
		
NOTE:
	
This issue has been addressed in the most current release of
InterBase on WinNT/95 version (WI-V4.2.1).  This is specific
to the "Super Server" architecture.  All versions of "Classic"
V4 architecture will exhibit this problem.

Server Response from: ETNASC03