The Scenario
============

say you have a database with 2 tables (Helen: this is NOT the same
message as #22869)

set term !! ;

CREATE DATABASE 'c:\cchim32\worlds.gdb' USER 'SYSDBA'
PASSWORD 'masterkey'!!

create table Stars (
Star_ID integer not null,
System_ID integer,
primary key (Star_ID)
)!!

create table Systems (
System_ID integer not null,
Planet_ID integer not null,
primary key (System_ID,Planet_ID)
)!!

insert into Systems values (1,1)!!
insert into Systems values (1,2)!!
insert into Systems values (2,1)!!
insert into Systems values (2,2)!!
insert into Stars values (1,1)!!
insert into Stars values (2,1)!!

set term ; !!

The Problem
===========

You want to add a set of rules such that System_ID in Stars will be
NULL or will be an existing value in Systems (System_ID)

Head Scratching
===============

Writing
ALTER TABLE Stars ADD FOREIGN KEY (System_ID) REFERENCES
Systems (System_ID) on update cascade on delete cascade

is out of question.

In fact, Firebird returns the error message "could not find unique
index with specified columns"

The Solution
============

After many trials, I added the triggers enclosed below.

They mimic the constraint I need, which is

ALTER TABLE Stars ADD DUILIO KEY (System_ID) REFERENCES Systems
(System_ID) on update cascade on delete cascade

The new DUILIO KEY will act like a FOREIGN KEY, but on the LAST
OCCURRENCE of System_ID in Systems.

In other words:

- when the last occurrence of System_ID=1 is deleted in Systems, all
rows with equal value in Stars will be deleted.
- when the last occurrence of System_ID=1 is changed to System_ID=3
in Systems, all rows with equal value in Stars will be modified
accordingly.

(Please note that DUILIO KEY can handle FOREIGN KEY as a special
case).

The Question
============

Seriously: is there any less cumbersome way to solve this simple and
often occurring problem ?


-------------------------- enclosed code -----------------------------
create trigger systems_aftdel for systems
after delete
position 10
as
declare variable ID integer;
declare variable tot integer;
begin
ID=systems.System_ID;

select count(*) from systems a where
a.System_ID=:ID into :tot;

if (tot=0) then
delete from Stars where Stars.System_ID=:ID;
end!!

create trigger systems_aftupd for systems
after update
position 10
as
declare variable oldID integer;
declare variable newID integer;
declare variable tot integer;
begin
oldID=old.System_ID;
newID=new.System_ID;

select count(*) from systems a where
a.System_ID=:oldID into :tot;

if (tot=0) then
update Stars set System_ID=:newID where System_ID=:oldID;
end!!


create trigger Stars_BefIns for Stars
before insert
position 10
as
declare variable ID integer;
begin
ID=Stars.System_ID;

if (ID is not null) then
if (not exists
(select * from Systems where
System_ID=:ID )) then
exception invalid_key;
end!!

create trigger Stars_BefUpd for Stars
before update
position 10
as
declare variable newID integer;
begin
newID=new.System_ID;

if (newID is not null) then
if (not exists
(select * from Systems where
System_ID=:newID )) then
exception invalid_key;
end!!