On Jun 5, 5:10 am, Lew <l...@xxxxxxxxxxxxxxxxxxxx> wrote: > Erwin Brandstetter wrote: > > CREATE TABLE king > > ( > > king_id INTEGER PRIMARY KEY REFERENCES man (man_id) ON UPDATE > > CASCADE ON DELETE CASCADE, > > nation_id INTEGER UNIQUE, > > FOREIGN KEY (man_id, nation_id) REFERENCES man (man_id, nation_id) > > ON UPDATE CASCADE ON DELETE CASCADE > > ); > > I like this. On Jun 5, 5:10 am, Lew <l...@xxxxxxxxxxxxxxxxxxxx> wrote: > Erwin Brandstetter wrote: > > CREATE TABLE king > > ( > > king_id INTEGER PRIMARY KEY REFERENCES man (man_id) ON UPDATE > > CASCADE ON DELETE CASCADE, > > nation_id INTEGER UNIQUE, > > FOREIGN KEY (man_id, nation_id) REFERENCES man (man_id, nation_id) > > ON UPDATE CASCADE ON DELETE CASCADE > > ); > > I like this. On a second inspection, I had a typo in the code above, and the second foreign key is redundant. So we get: CREATE TABLE king ( man_id INTEGER PRIMARY KEY, nation_id INTEGER UNIQUE, FOREIGN KEY (man_id, nation_id) REFERENCES man (man_id, nation_id) ON UPDATE CASCADE ON DELETE CASCADE ); (...) > > We are still avoiding circular references. > > I'm not so sure we need to avoid that. Yeah, I don't think we have to avoid it. But as it comes at no cost, I'd take it. I have commented on possible complications arising from circular references above. Regards Erwin
