On Jun 5, 5:10 am, Lew <l...@nospam.lewscanon.com> 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...@nospam.lewscanon.com> 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