unique constraint instead of primary key? what disadvantage(ODBC usage)?

Поиск
Список
Период
Сортировка
От george young
Тема unique constraint instead of primary key? what disadvantage(ODBC usage)?
Дата
Msg-id 20060208210455.5d6f5bf9.gry@ll.mit.edu
обсуждение исходный текст
Ответы Re: unique constraint instead of primary key? what  (Ken Hill <ken@scottshill.com>)
Re: unique constraint instead of primary key? what disadvantage(ODBC usage)?  ("BigSmoke" <bigsmoke@gmail.com>)
Список pgsql-sql
[PostgreSQL 8.1.0 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.0.1]
I'm designing a completely new schema for my database.  A major
criterion is that it facilitate ad-hoc queries via MS-access, excel and
OpenOffice, presumably with ODBC.

My question regards the use of UNIQUE constraints instead of PRIMARY
KEY's on some tables.  Both result in an index on the specified tuple
of fields, so I presume query performance shouldn't be much different.

Using UNIQUE constraints seems to let me better match the natural
structure of my data.  A 'run' contains a sequence of 'opsets'.
Each opset contains a sequence of (a few) 'step's.
  run-foo     opset-1        step-1        step-2     opset-2        step-1

So the 'steps' table is logically indexed by (run, opset_num, step_num).
But some opsets are not in runs, and some steps are not in opsets, so
I would have step.run be null in some cases, likewise step.opset_num.

Null values mean I can't use these fields in a primary key, so I
propose to use UNIQUE constraints instead.

What am I losing by not using PRIMARY KEYS?  Will ODBC clients have
difficulty dealing nicely with the database?  Will the planner produce
lousy query plans?  Will Edgar Codd haunt my dreams?

-- George Young


-- 
"Are the gods not just?"  "Oh no, child.
What would become of us if they were?" (CSL)


В списке pgsql-sql по дате отправления:

Предыдущее
От: Ken Hill
Дата:
Сообщение: Column Index vs Record Insert Trade-off?
Следующее
От: Ken Hill
Дата:
Сообщение: Re: Syntax for "IF" clause in SELECT