The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Index: Primary or Unique?

In MS SQL, is there any iportant difference between a PRIMARY index and a UNIQUE index?

The description of CREATE TABLE keeps saying "... blah blah blah for a PRIMARY or UNIQUE index ..."; and the ALTER TABLE statement lets me define a UNIQUE index but not a PRIMARY one.
Christopher Wells Send private email
Wednesday, September 27, 2006
 
 
> Alter Table lets me define a Unique index but not a Primary one

I was wrong: ALTER TABLE ADD CONSTRAINT lets me add a Primary or a Unique constraint. The only difference between Primary and Unique is that there can be only one Primary.

Anyway, what if anything is special about being *the* primary constraint instead of being *a* unique constraint?
Christopher Wells Send private email
Wednesday, September 27, 2006
 
 
Referential integrity.

A "foreign key" is supposed to point to a primary key, not to just any index. I don't remember if this distinction means anything in "straight" SQL, but e.g. in ksql and q (two versions of a query language from Kx systems), foreign key join is always automatic; e.g., in the standard supplier/parts database [ http://en.wikipedia.org/wiki/Suppliers_and_Parts_database , ], you can do:

  select from sp where s.city=p.city

instead of the standard SQL

  select sp.s,sp.p,sp.qty from s,p,sp where sp.s=s.s and sp.p=p.p and p.city=s.city

(renaming supplier=s, part=p, sells=sp to shorten the text).

ksql/q follows the "foreign key->primary key" lead and makes _all_ supplier and part columns virtually a part of the "sells" table.

While one can argue any unique index is just as good and be absolutely right, the "primary key" is unique in being the default link target of a "foreign key".
Ori Berger
Thursday, September 28, 2006
 
 
As Ori says, a primary constraint is used to define a primary key (which will normally also be unique). This is used for referential integrity - each foreign key points to the corresponding primary key.

A unique constraint is often used to define a natural key - a combination of one or more fields that uniquely define each row from an end-user point of view.
Mark Pearce Send private email
Thursday, September 28, 2006
 
 
Thanks; I thought (incorrectly perhaps) that any unique key could be the link target of a foreign key, and didn't realize that a foreign key could have a default link target.
Christopher Wells Send private email
Thursday, September 28, 2006
 
 
Don't know about MS SQL, but in Oracle, you can drop a unique index, but you have to drop the table to drop the primary key.

In practical terms, there really is no difference.
Steve Hirsch Send private email
Thursday, September 28, 2006
 
 
I could be wildly off, but IIRC you can make only one index on a MS SQL table 'clustered', which performs better b/c the data are organized around it. By default the primary key index is clustered, but if you're going to be searching on one or more fields more frequently it makes sense to specify that index as the clustered one.
Chris Winters Send private email
Thursday, September 28, 2006
 
 
Yes. I was thinking that it typically makes more sense to cluster on a foreign key (if there is one) than on the primary key, because (if there is a foreign key) you'll usually be doing things like "SELECT FROM Foo WHERE Foo.ForeignId = ".
Christopher Wells Send private email
Thursday, September 28, 2006
 
 
While it is true that you can not drop a primary key as you can an index, you do not need to drop the table in order to remove the primary key.
alter table table_name drop constraint constraint_name;
works on the Oracle databases I've used.

Thursday, September 28, 2006
 
 
You can only have 1 Primary Key per table. It is also a unique constraint.

You can have more than 1 Unique constraint per table. You do not have to apply an index to the column that the unique constraint is on.

You can only have one Clustered Index per table (because the Clustered Index controls the physical order of the rows in the table.

You can have more than 1 Non-Clustered Index on a table.

By default, SQL Server (2000 at least, I don't know if this is true with 2005) creates Primary Keys as Clustered. For Example:

    ALTER TABLE tblExample
    ADD CONSTRAINT PK_Example
    PRIMARY KEY (ExampleID)
    WITH FILLFACTOR = 90
    GO    

will create a clustered index called PK_Example on Column ExampleID in table tblExample.

If you want the Primary Key to be Non-Clustered, you should do something like the following:

    ALTER TABLE tblExample
    ADD CONSTRAINT PK_Example
    PRIMARY KEY NONCLUSTERED (ExampleID)
    WITH FILLFACTOR = 90
    GO    

A Clustered Index does not have to be Unique (SQL Server does some voodoo behind the scenes to allow this).

Inside SQL Server 2000 by Kalen Delaney does a great job explaining stuff like this. Regardless, read something and make sure you understand all of the complexities before designing a database for anything other bigger tracking your comic book collection.
analamous
Thursday, September 28, 2006
 
 
"A Clustered Index does not have to be Unique (SQL Server does some voodoo behind the scenes to allow this)."

The clusteriness of the index has nothing to do with uniqueness. A clustered index just means that the rows will be written to the DB file in increasing order, according to the value of the clustered column, allowing the query engine to perform binary searches to find records (and ranges of records) rather than having to perform an entire table scan.

It makes a **huge** speed difference, especially on queries that use comparison operators (> < <= >=) in the WHERE clause.
BenjiSmith Send private email
Thursday, September 28, 2006
 
 
For one thing a unique key can be nullable -- a primary key cannot.

A primary key should also be immutable, and a unique key need not be.
David Aldridge Send private email
Thursday, September 28, 2006
 
 
Got it. Thanks for your clarifications.
Christopher Wells Send private email
Thursday, September 28, 2006
 
 
Well...Actually:

"
Clustered indexes must be unique; since the data is ordered according to the values in the clustered index key, SQL Server must have a way to uniquely identify the position of each data row in the index. If you have duplicate values in the clustered key, SQL Server will add a hidden column (key) to the index to make it unique.
"

This is the "Voodoo" I mentioned.

From: http://www.informit.com/articles/article.asp?p=28285&rl=1 (and other places in my reading. This is just the first web stier I could find to reference).
analamous
Friday, September 29, 2006
 
 
For the really determined, one can read up in C.J. Date on "candidate keys", potentially more than one per relation, and which uniquely determine the values of tuple.

In Oracle it used to be (7.x? 8.0.x?) that you could point the foreign key of the child table to a unique key, not necessarily a primary key, of the parent table, BUT only if the parent table did have a primary key.

And yes, you can drop an Oracle primary key:

SQL> create table whizbang
  2  (val number,
  3  description varchar2(32),
  4  constraint pk$whizbang primary key (val)
  5  );

Table created.

SQL> insert into whizbang values(1, 'one');

1 row created.

SQL> /
insert into whizbang values(1, 'one')
*
ERROR at line 1:
ORA-00001: unique constraint (COPE.PK$WHIZBANG) violated


SQL> alter table whizbang drop constraint pk$whizbang;

Table altered.

SQL> insert into whizbang values(1, 'one');

1 row created.

SQL>
George Jansen Send private email
Friday, September 29, 2006
 
 
Huh. Thanks, analamous, for the clarification.

It's interesting that they need a uniqueness constraint just to put the records into field-ordered positions within the file. I wouldn't have thought that was necessary (or that adding some invisible bogus column data would solve the uniqueness problem).
BenjiSmith Send private email
Saturday, September 30, 2006
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz