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 ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
