Inheritance and shared sequence

Sebastjan Trepca wrote:

Hi,

as I understood, when you create a table which inherits some other
table, the constraints and indexes do not go to the child table hence
you have to create a separate ones in there. That means you cannot
depend that you won't have duplicate IDs in both tables. Right?

BUT...what if child table and parent table both use the same sequence
for the primary key. Would the duplication still be an issue?

Well, if you *always* use the sequence you'll be OK (until you run out
of numbers), but it won't stop you manually supplying your own values.

--
Richard Huxton
Archonet Ltd

On Thu, 2007-06-07 at 09:44 +0100, Richard Huxton wrote:

Sebastjan Trepca wrote:

Hi,

as I understood, when you create a table which inherits some other
table, the constraints and indexes do not go to the child table hence
you have to create a separate ones in there. That means you cannot
depend that you won't have duplicate IDs in both tables. Right?

BUT...what if child table and parent table both use the same sequence
for the primary key. Would the duplication still be an issue?

Well, if you *always* use the sequence you'll be OK (until you run out
of numbers), but it won't stop you manually supplying your own values.

If you must keep the primary key unique across a set of tables, you need
to create another table to index the keys and record which table each
key is in. Use triggers to keep the index table up to date.
--
Oliver Elphick olly@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA
========================================
Do you want to know God? http://www.lfix.co.uk/knowing_god.html