Обсуждение: There can be only one! How to avoid the "highlander-problem".

Поиск
Список
Период
Сортировка

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

От
Erwin Brandstetter
Дата:
Hi group!

In the course of trying to create a cleanly formated posting that would
make my problem understandable I have eventually solved it myself. :)
I now post the solution instead, maybe it is of interest to someone. :)

Here is a showcase how to avoid to the "highlander-problem".
Imagine a male, monarchistic world. Every people must have one (and only
one) king at _all_ times. This implementation aims to enforce these
rules as cleanly and swiftly as possible..


----- begin of code

CREATE TABLE mankind
(
  man_id integer primary key,
  people_id integer NOT NULL,              -- references table people
.., but that's irrelevant here ..
  king boolean NOT NULL DEFAULT false
);


---- Only one king per people ----
/* But no partial UNIQUE INDEX, because my solution needs temporary
"duplicates". Peoples will have to trust the triggers.
I _could_ implement it with a DEFERRED table constraint, IF partial
indices were supported with table constraints,
but they are not in pg 8.1.x or 8.2.x. Pseudo-Code example:
    ALTER TABLE mankind
     ADD CONSTRAINT mankind_people_uni_king_idx UNIQUE (people_id) WHERE
king[ = true]
     DEFERRABLE INITIALLY DEFERRED;
I create (a non-unique) index anyway, to speed up the triggers.
*/
CREATE INDEX mankind_king_idx
  ON mankind (people_id)
  WHERE king;


---- trigger BEFORE UPDATE ---- To keep it simple we make world racist.
Men cannot migrate.

CREATE OR REPLACE FUNCTION trg_mankind_upbef()
  RETURNS "trigger" AS
$BODY$
BEGIN

IF NEW.people_id <> OLD.people_id THEN              -- NOT NULL allows "<>"
    RAISE EXCEPTION 'This is a racist world! Men cannot migrate.';
END IF;

IF NEW.man_id <> OLD.man_id THEN                    -- NOT NULL allows "<>"
    RAISE EXCEPTION 'A man has only one life and cannot change his
identity.';
END IF;

RETURN NEW;

END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

CREATE TRIGGER upbef
  BEFORE UPDATE
  ON mankind
  FOR EACH ROW
  EXECUTE PROCEDURE trg_mankind_upbef();


---- trigger AFTER UPDATE ----

CREATE OR REPLACE FUNCTION trg_mankind_upaft()
  RETURNS "trigger" AS
$BODY$
DECLARE
    kings int4;
BEGIN

IF NEW.king <> OLD.king THEN                          -- NOT NULL allows
"<>"
   kings := count(*)::int4 FROM mankind WHERE people_id = NEW.people_id
AND king;
   raise warning '%', kings;
    IF kings = 1 THEN
            --do nothing;

    ELSIF kings < 1 THEN
        RAISE EXCEPTION 'You must make another man king to get rid of
the old king!';

    ELSIF kings > 1 THEN
        UPDATE mankind
            SET king = FALSE
         WHERE people_id = NEW.people_id
            AND man_id <> NEW.man_id                    -- God save the
new king!
            AND king;
    END IF;
END IF;

END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

CREATE TRIGGER upaft
  AFTER UPDATE
  ON mankind
  FOR EACH ROW
  EXECUTE PROCEDURE trg_mankind_upaft();


---- trigger BEFORE INSERT ----

CREATE OR REPLACE FUNCTION trg_mankind_insbef()
  RETURNS "trigger" AS
$BODY$
BEGIN

IF NOT EXISTS (SELECT 1 FROM mankind WHERE people_id = NEW.people_id) THEN
   NEW.king := true;                                    -- firstborn is
always king.
END IF;

RETURN NEW;

END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

CREATE TRIGGER insbef
  BEFORE INSERT
  ON mankind
  FOR EACH ROW
  EXECUTE PROCEDURE trg_mankind_insbef();


---- trigger AFTER INSERT ----

CREATE OR REPLACE FUNCTION trg_mankind_insaft()
  RETURNS "trigger" AS
$BODY$
DECLARE
    kings int4;
BEGIN

kings := count(*)::int4 FROM mankind WHERE people_id = NEW.people_id AND
king;

IF kings = 1 THEN
        --do nothing;

ELSIF kings > 1 THEN
    UPDATE mankind
        SET king = FALSE
     WHERE people_id = NEW.people_id
        AND man_id <> NEW.man_id                       -- God save the
new king!
        AND king;

ELSIF kings < 1 THEN                                   -- actually,
should never occur, because of trigger BEFORE INSERT
    UPDATE mankind
        SET king = TRUE
     WHERE man_id = NEW.man_id;                        -- the new man is
as good a king as any.

END IF;

END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

CREATE TRIGGER insaft
  AFTER INSERT
  ON mankind
  FOR EACH ROW
  EXECUTE PROCEDURE trg_mankind_insaft();


---- trigger AFTER DELETE ---- (if old king dies)

CREATE OR REPLACE FUNCTION trg_mankind_delaft()
  RETURNS "trigger" AS
$BODY$
BEGIN

 -- We trust the triggers and do not check if there was another king, as
there can be only one.
 -- AND NOT EXISTS (SELECT 1 FROM mankind WHERE people_id =
OLD.people_id AND king)
IF OLD.king THEN
   UPDATE mankind SET king = true
    WHERE man_id = (SELECT man_id FROM mankind WHERE people_id =
OLD.people_id LIMIT 1);
END IF;

END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

CREATE TRIGGER delaft
  AFTER DELETE
  ON mankind
  FOR EACH ROW
  EXECUTE PROCEDURE trg_mankind_delaft();

----- end of code


This solution is tested and works. Of course, there is always room for
improvement. So if you spot something, don't hesitate to point it out.
Any other feedback is welcome as well, of course.


Thanks for all your virtual help so far! ;)



Regards
Erwin


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

От
Erwin Brandstetter
Дата:
RETURN was missing in the AFTER triggers. here is the corrected version:

----- begin of code

CREATE TABLE mankind
(
 man_id integer primary key,
 people_id integer NOT NULL,              -- references table people ..,
but that's irrelevant here ..
 king boolean NOT NULL DEFAULT false
);


---- Only one king per people ----
/* But no partial UNIQUE INDEX, because my solution needs temporary
"duplicates". Peoples will have to trust the triggers.
I _could_ implement it with a DEFERRED table constraint, IF partial
indices were supported with table constraints,
but they are not in pg 8.1.x or 8.2.x. Pseudo-Code example:
   ALTER TABLE mankind
    ADD CONSTRAINT mankind_people_uni_king_idx UNIQUE (people_id) WHERE
king[ = true]
    DEFERRABLE INITIALLY DEFERRED;
I create (a non-unique) index anyway, to speed up the triggers.
*/
CREATE INDEX mankind_king_idx   ON mankind (people_id)
 WHERE king;


---- trigger BEFORE UPDATE ---- To keep it simple we make world racist.
Men cannot migrate.

CREATE OR REPLACE FUNCTION trg_mankind_upbef()
 RETURNS "trigger" AS
$BODY$
BEGIN

IF NEW.people_id <> OLD.people_id THEN              -- NOT NULL allows "<>"
   RAISE EXCEPTION 'This is a racist world! Men cannot migrate.';
END IF;

IF NEW.man_id <> OLD.man_id THEN                    -- NOT NULL allows "<>"
   RAISE EXCEPTION 'A man has only one life and cannot change his
identity.';
END IF;

RETURN NEW;

END;
$BODY$
 LANGUAGE 'plpgsql' VOLATILE;

CREATE TRIGGER upbef
 BEFORE UPDATE
 ON mankind
 FOR EACH ROW
 EXECUTE PROCEDURE trg_mankind_upbef();


---- trigger AFTER UPDATE ----

CREATE OR REPLACE FUNCTION trg_mankind_upaft()
 RETURNS "trigger" AS
$BODY$
DECLARE
   kings int4;
BEGIN

IF NEW.king <> OLD.king THEN                          -- NOT NULL allows
"<>"
  kings := count(*)::int4 FROM mankind WHERE people_id = NEW.people_id
AND king;
  raise warning '%', kings;
   IF kings = 1 THEN
           --do nothing;

   ELSIF kings < 1 THEN
       RAISE EXCEPTION 'You must make another man king to get rid of the
old king!';

   ELSIF kings > 1 THEN
       UPDATE mankind
           SET king = FALSE
        WHERE people_id = NEW.people_id
           AND man_id <> NEW.man_id                    -- God save the
new king!
           AND king;
   END IF;
END IF;

RETURN NULL;

END;
$BODY$
 LANGUAGE 'plpgsql' VOLATILE;

CREATE TRIGGER upaft
 AFTER UPDATE
 ON mankind
 FOR EACH ROW
 EXECUTE PROCEDURE trg_mankind_upaft();


---- trigger BEFORE INSERT ----

CREATE OR REPLACE FUNCTION trg_mankind_insbef()
 RETURNS "trigger" AS
$BODY$
BEGIN

IF NOT EXISTS (SELECT 1 FROM mankind WHERE people_id = NEW.people_id) THEN
  NEW.king := true;                                    -- firstborn is
always king.
END IF;

RETURN NEW;

END;
$BODY$
 LANGUAGE 'plpgsql' VOLATILE;

CREATE TRIGGER insbef
 BEFORE INSERT
 ON mankind
 FOR EACH ROW
 EXECUTE PROCEDURE trg_mankind_insbef();


---- trigger AFTER INSERT ----

CREATE OR REPLACE FUNCTION trg_mankind_insaft()
 RETURNS "trigger" AS
$BODY$
DECLARE
   kings int4;
BEGIN

kings := count(*)::int4 FROM mankind WHERE people_id = NEW.people_id AND
king;

IF kings = 1 THEN
       --do nothing;

ELSIF kings > 1 THEN
   UPDATE mankind
       SET king = FALSE
    WHERE people_id = NEW.people_id
       AND man_id <> NEW.man_id                       -- God save the
new king!
       AND king;

ELSIF kings < 1 THEN                                   -- actually,
should never occur, because of trigger BEFORE INSERT
   UPDATE mankind
       SET king = TRUE
    WHERE man_id = NEW.man_id;                        -- the new man is
as good a king as any.

END IF;

RETURN NULL;

END;
$BODY$
 LANGUAGE 'plpgsql' VOLATILE;

CREATE TRIGGER insaft
 AFTER INSERT
 ON mankind
 FOR EACH ROW
 EXECUTE PROCEDURE trg_mankind_insaft();


---- trigger AFTER DELETE ---- (if old king dies)

CREATE OR REPLACE FUNCTION trg_mankind_delaft()
 RETURNS "trigger" AS
$BODY$
BEGIN

-- We trust the triggers and do not check if there was another king, as
there can be only one.
-- AND NOT EXISTS (SELECT 1 FROM mankind WHERE people_id = OLD.people_id
AND king)
IF OLD.king THEN
  UPDATE mankind SET king = true
   WHERE man_id = (SELECT man_id FROM mankind WHERE people_id =
OLD.people_id LIMIT 1);
END IF;

RETURN NULL;

END;
$BODY$
 LANGUAGE 'plpgsql' VOLATILE;

CREATE TRIGGER delaft
 AFTER DELETE
 ON mankind
 FOR EACH ROW
 EXECUTE PROCEDURE trg_mankind_delaft();

----- end of code

/Erwin

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

От
Erwin Brandstetter
Дата:
On Jun 2, 2:43 am, brandstet...@falter.at (Erwin Brandstetter) wrote:
>   raise warning '%', kings;

And remove this line of debug code.

/Erwin


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

От
Lew
Дата:
Erwin Brandstetter wrote:
> CREATE TABLE mankind
> (
>  man_id integer primary key,
>  people_id integer NOT NULL,              -- references table people .., but that's irrelevant here ..
>  king boolean NOT NULL DEFAULT false
> );

The trouble with this is that it models "kingship" as an attribute of every
man.  (What, no female rulers allowed?)  The overhead of being "not king" is
carried in every "mankind" record. This may suffice for your particular model,
but if you were designing for evolution you'd have a problem.  Every new
attribute of "mankind" would need a new column in the table - "isDuke",
"isNoble", "isHogSlopCleaner".

I would model "kingship" (or other attributes) in a separate table and use
PRIMARY KEY to enforce, or a TRIGGER - there is a large, possibly unbounded
set of ways to do this.

Here's one attempt, feel free to rate it good, bad or ugly (I am ignoring my
mistrust of auto-incremented integer surrogate keys):

CREATE TABLE mankind
(
   man_id INTEGER PRIMARY KEY
   -- kings belong to nations, not vice versa
   -- , other information about a man
);
CREATE TABLE nationalities
(
   man_id INTEGER FOREIGN KEY REFERENCES mankind ( man_id ),
   nation_id INTEGER FOREIGN KEY REFERENCES nations ( nation_id ),
   PRIMARY KEY ( man_id, nation_id )
);
CREATE TABLE nations
(
   nation_id INTEGER PRIMARY KEY
   , king INTEGER FOREIGN KEY REFERENCES mankind ( man_id )
   -- , other information about a nation
   , FOREIGN KEY ( king, nation_id )
     REFERENCES nationalities ( man_id, nation_id )
);

The circular foreign-key relationships might be problematic - would someone
comment on that?

To handle that I would ensure that any transaction that updates
"nations (king)" checks that the pretender's "man_id" is already correctly
entered in "nations".

--
Lew

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

От
Erwin Brandstetter
Дата:
Hi Lew!

Thank you for your comments. I have elaborated on them.

On Jun 3, 7:22 pm, Lew <l...@nospam.lewscanon.com> wrote:
(...)
> The trouble with this is that it models "kingship" as an attribute of every
> man.  (What, no female rulers allowed?)

Yeah, saddening, isn't it? Actually, for simplicity's sake I
restricted my model to a "male, monarchistic world".


> The overhead of being "not king" is
> carried in every "mankind" record. This may suffice for your particular model,
> but if you were designing for evolution you'd have a problem.  Every new
> attribute of "mankind" would need a new column in the table - "isDuke",
> "isNoble", "isHogSlopCleaner".


You are right, of course. (I switch to "nation" instead of "people" in
my examples like you did, as the term seems clearer.)

However, in your SQL model, you extracted nationality instead of
kingship. If every man has to be member of exactly one nation (which I
postulate), nationality can reside with the man. (we need
man.nation_id instead of nation.man_id) That leaves only the kingship
to be allocated. I postulate further that a king only be king of his
own people (rules out multiple kingships, too). So the "king" needs
only to have 1 attribute: man_id.
To make room for other roles, as you mentioned, I include a role_id.
However, roles must be as unique like the kingship. To enforce
uniqueness of one king (or other role) per nation I include the
seemingly redundant nation_id and impose a UNIQUE (nation_id, role_id)
on it.
To enforce that a man can only become king of his own people, I wrap
both (man_id, nation_id) in a FOREIGN KEY constraint on "man".
PostgreSQL therefore requires a corresponding (redundant) UNIQUE
(nation_id, role_id) on "man".
!NOTE that I do NOT reference table "nation", so we have no circular
foreign-key constraints!


0.) Lets number the models:

CREATE TABLE nation
(
   nation_id INTEGER PRIMARY KEY
);

CREATE TABLE man
(
   man_id INTEGER PRIMARY KEY,
   nation_id INTEGER NOT NULL REFERENCES nation (nation_id) ON UPDATE
CASCADE ON DELETE CASCADE
);

CREATE TABLE role  -- "role" is non-reserved word in postgresql or
SQL2003, but reserved in SQL99
(
   man_id INTEGER PRIMARY KEY REFERENCES man (man_id) ON UPDATE
CASCADE ON DELETE CASCADE,
   nation_id INTEGER,
   role_id INTEGER,
   UNIQUE (nation_id, role_id)
   FOREIGN KEY (man_id, nation_id) REFERENCES man (man_id, nation_id)
ON UPDATE CASCADE ON DELETE CASCADE
);


This makes sense if we have a lot of men per nation and an unknown
number of unique roles per nation. I will simplify this model step by
step now, along with simplified conditions:


1.) First, lets get rid of multiple roles. My model only needs
kingship. So I replace table "role" with the following table
"king" (the rest is the same). :

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
);


2.) Now we can further simplify the structure. Skip the table "king"
and merge kingship as an attribute into table "man". This makes sense
with one (or a small number of ) known role(s).
Adds a field to _every_ man and gets rid of one tuple per king and the
overhead for that extra table.
Whether this is preferable over 1.) depends on the typical number of
men per nation. If there is more than just a few, you should stick to
1.). If there is only a few, however, you gain something.
Note, how we reference nation(nation_id) twice (!), but only one time
is NOT NULL.
We are still avoiding circular references.

CREATE TABLE man
(
   man_id INTEGER PRIMARY KEY,
   nation_id INTEGER NOT NULL REFERENCES nation (nation_id) ON UPDATE
CASCADE ON DELETE CASCADE,
   king_id INTEGER UNIQUE REFERENCES nation (nation_id) ON UPDATE
CASCADE ON DELETE CASCADE,
   CHECK ((nation_id = king_id))      --  needed to make sure a man
can only become king of his own people.
);


3.) As an improvement over 2.) we can merge kingship into nation (as
you suggested).
Note the "ON DELETE SET NULL" clause, that allows a king to die.
Actually I would pass on kingship to another man (or NULL if none are
left) per trigger, much like in my initial post:
"trg_mankind_delaft()".
Note also that king_id isn't "NOT NULL", so we need to be prepared for
nations without a king (king_id IS NULL).
To enforce a king we'd set it "NOT NULL DEFAULT 0", but then we'd need
a dummy man with man_id = 0 to serve referential integrity and that's
where the circular references begin to bite. Because the dummy man
needs a nation first. This could only be solved by entering a dummy
nation and a dummy man before enforcing referential integrity.
We also need triggers BEFORE INSERT AND UPDATE to check that the king
is member of the nation
    IF NEW.king_id IS NOT NULL AND nation_id IS DISTINCT FROM
NEW.nation_id FROM man WHERE man_id = NEW.king_id THEN
       RAISE EXCEPTION 'Usurper!';
    END IF;
Now we have to store only one field per nation and not per man.


CREATE TABLE nation
(
   nation_id INTEGER PRIMARY KEY
   king_id INTEGER REFERENCES man (man_id) ON UPDATE CASCADE ON DELETE
SET NULL
);

CREATE TABLE man
(
   man_id INTEGER PRIMARY KEY,
   nation_id INTEGER NOT NULL REFERENCES nation (nation_id) ON UPDATE
CASCADE ON DELETE CASCADE
);


4.) Finally, we can simplify the model 2.) in another way and avoid
the circular references. As man.king_id only ever references the same
nation.nation_id as man.nation_id, we can substitute the integer with
a (smaller) boolean and get rid of the FOREIGN KEY constraint and the
CHECK constraint. We still need to take care of the UNIQUEness of
kingship per nation, though. That's where my initially posted code
example comes in.

Also the overhead of being "not king" has to be carried by every man
instead of every nation, as you mentioned. BUT in my case, there are
fewer men than nations - only 0.7 per nation on average. Yeah, more
than half of the "nations" have no men, and no nation has more than
handful. That's where the analogy to "nation" fails and my example is
a bit misleading. I have chosen it because everyone understands the
"highlander problem" without much explanation: there can be only
one. :)

I think, all of the four models make sense under the right
circumstances. 3.) or 4.) are my choices.

I have implemented 3.) a number of times in the past. It works, but
you have to pay attention to the circular references.
Now I have come up with the new 4.) and it works like a charm. So far.
Even more so, as I have _many_ SELECTs (often only including "nation"
and not so often also "man"), few INSERTs and DELETEs, and rare
UPDATEs.
In the operations on "man", >99% of the INSERTs only need to operate
on one tuple, and all of them only need to manipulate the much smaller
"man"-table. As there is more trigger work in 4.) versus more checking
of referential integrity in 3.), I am not really sure, which one is
the winner, though. Overhead for UPDATEs or SELECTs should be roughly
the same as for 3.).
As for SELECTS, 3.) should be slightly faster than 4.), but no real
   nation join man on nation.king_id = man.man_id
   nation join man USING (nation_id) WHERE king
And storage (in my case): 0.7 x 1 Byte clearly beats 1 x 4 Bytes.
(half of which are NULL though, so use only a bit of the NULL-bitmask)

OK, I may have gotten carried away. Hopefully all of this is of any
use to anyone - or at least of interest to you, Lew.


Regards
Erwin


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

От
Gregory Stark
Дата:
"Erwin Brandstetter" <brsaweda@gmail.com> writes:

> I postulate further that a king only be king of his own people (rules out
> multiple kingships, too).

That's not how it's worked in the past :)

If you have a nation table wouldn't you just have a king_id column in that
table which is a foreign key reference to man_id?

--
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com


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

От
Erwin Brandstetter
Дата:
On Jun 5, 8:35 am, s...@enterprisedb.com (Gregory Stark) wrote:
> "Erwin Brandstetter" <brsaw...@gmail.com> writes:
> > I postulate further that a king only be king of his own people (rules out
> > multiple kingships, too).
>
> That's not how it's worked in the past :)

Yeah i know. :) That's why I had to postulate this one explicitly.


> If you have a nation table wouldn't you just have a king_id column in that
> table which is a foreign key reference to man_id?

Have a look at my model 3.) above ..

Regards
Erwin


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

От
Erwin Brandstetter
Дата:
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


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

От
Erwin Brandstetter
Дата:
Oh my, it took me a ton of text to finally come up with a better idea.

5.) The Sun King solution
"L'etat c'est moi!". The model is as simple as can be:

CREATE TABLE nation
(
   nation_id SERIAL PRIMARY KEY
);
CREATE TABLE man
(
   man_id SERIAL PRIMARY KEY,
   nation_id INTEGER NOT NULL REFERENCES nation (nation_id) ON UPDATE
CASCADE ON DELETE CASCADE
);

Instead of drawing his man_id from the sequence, a king copies the
nation_id. Kingship can be tested by (man.man_id = nation.nation_id)
or (man.man_id = man.nation_id).
(Yeah, he is bound to come to mind here: http://en.wikipedia.org/wiki/Sun_King)
If you know the man_id of the king, you also know the nation_id, and
vice versa.

The caveat is, you have to make sure that the two sequences for nation
and man yield mutually exclusive values. One absolutely reliable way
would be to attach both primary keys to one sequence. This just works.

But, you don't have to stop at that. If you can guarantee that nation
will never burn more that, say, 100 000 nation_id's, and sequence wrap-
around is otherwise no concern, you can keep two separate sequences,
start nation_id at 1 and man_id at 100 000. Now you also know a king
when you see one: (man_id < 100 000) is king.

If the kingship of a nation is passed around, though, this can be a
problem. You could guard yourself against that with ON UPDATE CASCADE
for every foreign key constraint referencing man.man_id. But it would
be asking for trouble, still.

If you can meet both conditions - I have such cases here -, then go
with this one. Fastest, simplest, smallest.


Regards
Erwin