Search Postgresql Archives

Re: Surrogate VS natural keys

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




On Jun 20, 2007, at 11:28 , Joshua D. Drake wrote:

Richard Broersma Jr wrote:

I've often wondered about this. Since PostgreSQL allows FOREIGN KEYS to be referenced from UNIQUE (non-primary) natural keys, couldn't the schema be designed so that every table has a surrogate PRIMARY KEY and yet still maintain the relationships using UNIQUE natural keys.
Would a design like this be practical?

I would do it the other way. Have your primary keys be natural.

The albeit small advantage of using PRIMARY KEY on your surrogate if you're using the surrogate for foreign key constraints is that you can leave off the column name when using REFERENCES: it'll default to the PRIMARY KEY column(s). For example:

CREATE TABLE foos
(
    foo_id SERIAL PRIMARY KEY
    , foo TEXT NOT NULL
    , bal TEXT NOT NULL, UNIQUE (foo, bal)
);

CREATE TABLE bars
(
    bar_id SERIAL PRIMARY KEY
    , bar TEXT NOT NULL
    , foo_id INTEGER NOT NULL
        REFERENCES foos
);

\d bars
                           Table "public.bars"
Column |  Type   |                       Modifiers
--------+--------- +-------------------------------------------------------
bar_id | integer | not null default nextval('bars_bar_id_seq'::regclass)
bar    | text    | not null
foo_id | integer | not null
Indexes:
    "bars_pkey" PRIMARY KEY, btree (bar_id)
Foreign-key constraints:
    "bars_foo_id_fkey" FOREIGN KEY (foo_id) REFERENCES foos(foo_id)

It does work for multi-column primary keys as well:

CREATE TABLE baz_quuxen
(
    baz TEXT NOT NULL
    , quux TEXT NOT NULL
    , PRIMARY KEY (baz, quux)
);

CREATE TABLE blurfls
(
    blurfl TEXT PRIMARY KEY
    , baz TEXT NOT NULL
    , quux TEXT NOT NULL
    , FOREIGN KEY (baz, quux) REFERENCES baz_quuxen
);

\d blurfls
  Table "public.blurfls"
Column | Type | Modifiers
--------+------+-----------
blurfl | text | not null
baz    | text | not null
quux   | text | not null
Indexes:
    "blurfls_pkey" PRIMARY KEY, btree (blurfl)
Foreign-key constraints:
"blurfls_baz_fkey" FOREIGN KEY (baz, quux) REFERENCES baz_quuxen (baz, quux)

Having PRIMARY KEY on your natural key does provide some additional documentation.

Michael Glaesemann
grzm seespotcode net




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux