Subject Re: [ib-support] Re: going beyond FOREIGN KEY ... REFERENCES
Author Lucas Franzen

duilio_fos schrieb:
> Lucas,
> >What you want to do can perfectly be done with foreign keys
> mmm...
> >you add a STAR table above your SYSTEM table, like:
> could you please expand on this ?
> I am afraid I didn't understand.
> We start with this database:

create table Starsystems (
System_ID integer not null,
system_name varchar(40),
primary key ( System_ID )

create table Stars (
Star_ID integer not null,
System_ID integer,
Star_Name varchar(40),
primary key (Star_ID)

alter table stars
add constraint fk_stars_system
foreign key ( system_id ) references starsystems
on delete cascade;

create table Planets (
Planet_ID integer not null,
Star_ID integer,
Planet_Name integer not null,
primary key (Planet_ID)

alter table planets
add constraint fk_planets_stars
foreign key ( star_id ) references stars
on delete cascade;

delete a star and you'll delete all planets (and have an empty
starsystem then).
delete a starsystem and you'll delete all stars which itself will delete
all planets.

> Are you suggesting to insert a 3th table btw Stars and Systems ?

No, above.
If it's master detail.

If you want many to many you need a third table between.