Re: two primairy key in one table ? - Mailing list pgsql-general

On Thu, 04 Jul 2002 15:33:11 +0200, Angela Luyf <a.c.luyf@amc.uva.nl>
wrote:
>Hello,
>I have a database model where a many to many relation is used, so i need
>to create a table with two primary key. Don't know how to solve this in
>postgress, can't find it in the tutorials, so can anybody help me with
>this ?
>
Angela,

you most probably need a primary key with two columns.  Does this come
near to what you want?

CREATE TABLE a (
    id INT NOT NULL PRIMARY KEY
    /* , anything else ... */
);

CREATE TABLE b (
    id INT NOT NULL PRIMARY KEY
    /* , anything else ... */
);

CREATE TABLE atob (
    a_id INT NOT NULL REFERENCES a,
    b_id INT NOT NULL REFERENCES b,
    PRIMARY KEY (a_id, b_id)
);

For performance reasons you might want to
    CREATE UNIQUE INDEX idx_atob ON atob (b_id, a_id);

HTH.
Servus
 Manfred