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

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