Skip site navigation (1) Skip section navigation (2)

Inheritance and primary keys

From: Raphael Bauduin <raphael(dot)bauduin(at)be(dot)easynet(dot)net>
To: pgsql-admin(at)postgresql(dot)org
Subject: Inheritance and primary keys
Date: 2002-07-18 08:38:25
Message-ID: 20020718083825.GA607@raphael (view raw or flat)
Thread:
Lists: pgsql-admin
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 :-))

pgsql-admin by date

Next:From: Raphael BauduinDate: 2002-07-18 08:47:57
Subject: Inheritance and primary keys
Previous:From: Ludwig LimDate: 2002-07-18 07:57:40
Subject: Re: Database Diagrams.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group