Inheritance and primary keys

From: Raphael Bauduin <raphael(at)be(dot)easynet(dot)net>
To: pgsql-admin(at)postgresql(dot)org
Subject: Inheritance and primary keys
Date: 2002-07-18 08:47:57
Message-ID: 20020718084757.GA1160@raphael
Views: Raw Message | Whole Thread | Download mbox | Resend email
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 :-))

Browse pgsql-admin by date

  From Date Subject
Next Message Stefan Holzheu 2002-07-18 10:02:17 Re: Database Diagrams.
Previous Message Raphael Bauduin 2002-07-18 08:38:25 Inheritance and primary keys