Re: There can be only one! How to avoid the "highlander-problem".

On Jun 5, 5:10 am, Lew <l(dot)(dot)(dot)(at)nospam(dot)lewscanon(dot)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(dot)(dot)(dot)(at)nospam(dot)lewscanon(dot)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