I already asked this question on SO, prior to realizing that there may have been a more suitable place.
I am trying to implement an
IS-A relationship between tables. Say I have a
singer and a
band which are artists
I want every artist to have an
ID which is inherited by the
In the code I wrote it appears that a
band and a
singer may have the same
ID. How do I prevent that? And is there a better way to implement this? Reading material is most certainly welcome.
CREATE TABLE artists ( id number not null primary key ); CREATE TABLE singers ( id number not null primary key, name char(50) not null, last_name char(50) not null, foreign key(id) references artists(id) ); CREATE TABLE bands ( id number not null primary key, name char(50) not null unique, contact char(50) not null, participants number not null, check (participants > 0), foreign key(id) references artists(id) ); insert into artists(id) values(1); insert into singers(id, name, last_name) values(1, 'an', 'Bish'); insert into bands(id, name, contact, participants) values(1,'un','an',2);
as you can see I was able to add a band and a singer with the same ID, under the same "parent."