Hi,

> 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"

Your metadata is wrong - what is a "system"? If it cannot be uniquely
identified by a "systemID" alone? It always has a PlanetID??


With regards,

Martijn Tonies
InterBase Workbench - the developer tool for InterBase & Firebird
Firebird Workbench - the developer tool for Firebird
Upscene Productions
http://www.upscene.com

"This is an object-oriented system.
If we change anything, the users object."