Diff B/W PK and Unique Index.

[irehman]

Simple Question...

What is the difference between Unique Index and Primary Key. Primary Key also creates the Index.

Examples would be appreciated.

Thanks In Advance.

[chrisrlong]

A Pk is often supported by a unique index.

The theoretical difference is slight. A table can must have at least 1 candidate key. A candidate key is a column or set of columns that can be used to uniquely identify a row. Of course, a table can have multiple candidate keys as well. Each candidate key can and probably should be enforced via a unique index. Only 1 of the candidate keys can be the Primary (candidate) Key, hence the name. When relationships are made from that table, it is the primary key that is used to enforce the relationship.

For example, in a table Dept, both the department ID and the department name may be candidate keys. The Department ID will likely be chosen as the Primary Key with the department name then simply becoming an Alternate (candidate) Key. Both would be enforced via unique indexes.

In the Emp table, we store the department that the emp works in. There is, therefore, a relationship between emp and Dept. This relationship is many-1 (m:1), and such a relationship is enforced by migrateing the primary key from the parent (1) table to the child (m) table. Therefore, the emp table will then contain the primary key of the dept table - the department id column.

For more info, you might want to find a good book on relational theory.


HTH,

- Chris

[jmodic]

Just to add my $0.02 to Chris's thoughts...

Ther is one additional fundamental difference between PK and UKs. All columns that comprise PK must also be defined as NOT NULL, while this is not necessary for UKs. UKs still enforce uniqueness even if they contain NULLs, but prevent same NOT NULL + NULL combinations. For example, you have two collumns (C1 and C2) in UK, where C2 can have NULL values. So you can have the following pairs of values stored in those two collumns: (1, 2), (1,3), (2,1), (2, NULL). Now if you want to insert (1, NULL), UK will let you do so, while it will prevent you to insert (2, NULL), as this combination is allready contained in the table.

[ShrutiM]

But what if the unique key comprises of just one column?

This allows multiple inserts into the unique column with nulls.

I read some where that when there is a NULL in unique column then the unique key is not enforced.

[Halo]

Jmodic is correct. PK=Unique + Not Null, while Unique=Unique+(can be Null)

[chrisrlong]

Basically, I have rarely heard of a good reason to allow a NULL into a unique key. It definitely presents the issues raised here and IMHO should be avoided if at all possible, IMHO

- Chris

[inosov]

BTW, the original question was about unique index, not unique key.

"What is the difference between Unique Index and Primary Key.
Primary Key also creates the Index."

Primary key is the data integrity constraint, Unique index itself is not
a data integrity constraint but only the instrument to validate uniqueness;

Both Primary Key and Unique Key constraints create Unique Indexes
transparently, also Unique Indexes are dropped automatically and created
again if someone disables/enables these constraints.

You also can use Unique Index independently of any constraints
(Oracle, however, does not recommend to do this too often
due to conceptual reasons).

In Oracle8i, for example, you can create Function-Based Unique Index
which will validate quite complicated constraint, which can not be created
using standard data integrity constraints.

Example: if you need that the first 2 letters of ename be always different,
create Unique Index like this:

connect scott/tiger
create unique index emp_ind on emp(substr(ename,1,2));

(If you get error ORA-1031 here, grant query rewrite to scott

[jmodic]

Originally posted by ShrutiM
But what if the unique key comprises of just one column?

This allows multiple inserts into the unique column with nulls.

I read some where that when there is a NULL in unique column then the unique key is not enforced.

No, it will not allow multiple rows with NULL value in the unique index column. In single column unique index you can have at most one row with NULL in the indexed column

[inosov]

I don't agree with jmodic. See the following example:

SQL> connect scott/tiger
SQL> create unique index comm_idx on emp(comm);

Index created.

SQL> select count(*)
2 from emp
3 where comm is null;

COUNT(*)
----------
10

[jmodic]

I stand corrected...