|
Home > Archive > SQL Anywhere database > July 2005 > SQL Anywhere problem - self-joins & foreign keys
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
SQL Anywhere problem - self-joins & foreign keys
|
|
|
| Sorry, this is an old version but I do NOT have a choice - 5.0.02 Build
#583, running on XP Pro.
I have a table with three self-joins - for the father, the mother and
who the animal really is (think pedigrees). So, it contains the main
ID plus three other columns for which the main ID is a foreign key. In
other words you can only specify an animal in the database as the sire
or dam of another animal in the database, though you may not know the
sire and/or dam of any given animal.
There is another separate table which is a master of this one and there
there is no problem with that one - I haven't included any details from
it.
Here is a bit of SQL showing the problem (There are comments explaining
it and I'm running it in Infomaker with the delimiter changed to '!'):
// Drop the table if it exists
if exists ( select table_name from systable where table_name =
'swtable') then
drop table swtable;
end if!
// create the table
create table swtable
(
MainID integer NOT NULL default autoincrement,
Description char( 30 ) not null,
FK1 integer null,
FK2 integer null,
FK3 integer null,
PRIMARY KEY (MainId)
)!
alter table swtable add foreign key fk1 references swtable(mainid)!
alter table swtable add foreign key fk2 references swtable(mainid)!
alter table swtable add foreign key fk3 references swtable(mainid)!
//select * from sysforeignkey
// where foreign_table_id in (
// select table_id
// from systable where table_name = 'swtable' )!
// These rows take MainID values 1 and 2
insert into swtable values( default, 'fred', null, null, null )!
insert into swtable values( default, 'joe', null, null, null )!
// This should not be possible as 99 is not the primary key
// for any row in the table
update swtable
set fk1 = 99 where description = 'fred'!
//update swtable
// set fk1 = null where description = 'fred'!
//
select * from swtable!
| |
|
| OK. A good night's sleep and some more RTFM solved the problem - the
foreign keys were joining MainID to MainID... Doh!
|
|
|
|
|