Thread: Inheritance and primary keys

Hi,

I'm developing a little project management tool in which you have contacts
related to a customer, and contacts related to the project.  Both contacts
contains the same data for now, but for my test, I made the asumption that I
would link the contacts directly with the tables customer and project (so
customer_contactshas a field cust_idwhich is a foreig key). This is somwhat
similar to the example given inthe tutorial with cities, some of which being
capitals.

I thus create a table contacts, from which the tables project_contacts
and cutomer_contacts wil inherit:

CREATE TABLE "contacts" (
        "contact_id" serial NOT NULL,
        "contact_first_name" text,
        "contact_name" character varying(100),
        "contact_phone" character varying(100),
        "contact_email" character varying(100),
        "contact_mobile" character varying(100),
        Constraint "contacts_pkey" Primary Key ("contact_id")
);

When creating the children table, the primary key is not inherited, so I
specified a constaint about it (and I removed the foreign key constraint from
the text below):

create table project_contacts ( project_id bigint,Constraint "project_contacts_pkey" Primary Key
+("contact_id")) INHERITS (contacts);

create table customer_contacts ( cust_id bigint,Constraint "customer_contacts_pkey" Primary Key
+("contact_id")) INHERITS (contacts);


Now the strange thing (for me :-)
When inserting rows in the different tables, I can get several rows with the same contact_id, though they use
+the same sequence to set the value of the field contact_id as shown below:

\d contacts
                                             Table "contacts"
       Column       |          Type          |                          Modifiers
------------------+------------------------+-------------------------------------------------------------
 contact_id         | integer                |not null default nextval('"contacts_contact_id_seq"'::text)


\d customer_contacts;
                                         Table "customer_contacts"
       Column       |          Type          |                          Modifiers
--------------------+------------------------+-----------------------------------------------------------
 contact_id         | integer                |not null default nextval('"contacts_contact_id_seq"'::text)

\d project_contacts
                                         Table "project_contacts"
       Column       |          Type          |                          Modifiers
------------------+------------------------+-------------------------------------------------------------
 contact_id         | integer                |not null default nextval('"contacts_contact_id_seq"'::text)


But as shown below, you can have multiple rows with the same contact_id:

select contact_id from contacts;
 contact_id
------------
          2
          3
          4
          5
          6
          7
          8
          9
         10
         11
          5
          6
          8
          9
         10
         11
         12
         13
         14
         15
         16
         17
         18
         19
         20
          1
(26 rows)


Am I missing something? Should I read a little more about OO databases?

Thanks in advance for your help.

Raph


PS: to those who read the mail to this point, thanks already :-))