Re: unique constraint instead of primary key? what - Mailing list pgsql-sql

On Wed, 2006-02-08 at 21:04 -0500, george young wrote: <blockquote type="CITE"><pre>
<font color="#000000">[PostgreSQL 8.1.0 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.0.1]</font>
<font color="#000000">I'm designing a completely new schema for my database.  A major</font>
<font color="#000000">criterion is that it facilitate ad-hoc queries via MS-access, excel and</font>
<font color="#000000">OpenOffice, presumably with ODBC.</font>

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

<font color="#000000">Using UNIQUE constraints seems to let me better match the natural</font>
<font color="#000000">structure of my data.  A 'run' contains a sequence of 'opsets'.</font>
<font color="#000000">Each opset contains a sequence of (a few) 'step's.</font>

<font color="#000000">   run-foo</font>
<font color="#000000">      opset-1</font>
<font color="#000000">         step-1</font>
<font color="#000000">         step-2</font>
<font color="#000000">      opset-2</font>
<font color="#000000">         step-1</font>

<font color="#000000">So the 'steps' table is logically indexed by (run, opset_num, step_num).</font>
<font color="#000000">But some opsets are not in runs, and some steps are not in opsets, so</font>
<font color="#000000">I would have step.run be null in some cases, likewise step.opset_num.</font>

<font color="#000000">Null values mean I can't use these fields in a primary key, so I</font>
<font color="#000000">propose to use UNIQUE constraints instead.</font>

<font color="#000000">What am I losing by not using PRIMARY KEYS?  Will ODBC clients have</font>
<font color="#000000">difficulty dealing nicely with the database?  Will the planner produce</font>
<font color="#000000">lousy query plans?  Will Edgar Codd haunt my dreams?</font>

<font color="#000000">-- George Young</font>


</pre></blockquote> I think I can give you some insights about MS Access to help you. In MS Access, you can specify a
columnas a "primary key"; which basically means the column is indexed and must contain unique values (also, nulls are
notallowed). I have run into problems depending on columns being "primary key" in MS Access in db apps that receive
datain batch file uploads from other sources (e.g., uploading 1,000+ records into a table). <br /><br /> Is sounds like
yourrequirement to use MS Access for ad-hoc queries means that you will have some users that want to access the
databasewith MS Access as a "front-end" client tool. If that is the situation, then you don't need to worry about the
structureof the table as MS Access relies on ODBC for this. You may also want to communicate to the end users that MS
Accessis not a client-server tool; in other words, all of the records are transferred from the server to the client's
boxand then the query is executed.<br /><br /> I hope that helps.