What is the basic difference between primary key and (unique key along with Not NULL).

Questions by globalanil18   answers by globalanil18

Showing Answers 1 - 14 of 14 Answers

Ritesh Raj Singh

  • Mar 29th, 2007
 

Hi 


Basic Diffrence between primary key and unique key is the capability of unique key

for storing null values, a primary key on a column gurantees that the column must contain a value for a given row, but a unique key only guarantees that a column should be unique.


Apart from this difference , in oracle unique and primary key creates difference in

performance

e.g :  a primary key on a table enhance capability of using index

         on select count(*) from table_name  only if the table is analyzed

but a unique key won't

  Was this answer useful?  Yes

ggk.krishna

  • Apr 3rd, 2007
 


Hi Anil,
 Apart from NOTNULL, I can tell you one more difference.

We can define Primary key on only one column in a table.
But for Unique key there is no limit. we can define as many columns as we want.

  Was this answer useful?  Yes

Maria

  • Apr 14th, 2007
 

primary key = unique + not null + the only one PK column in the table + index on the PK column Created index enchances the queries on the PK column.

  Was this answer useful?  Yes

glakshkar

  • Sep 23rd, 2007
 


Hi Anil,
 Apart from NOTNULL, I can tell you one more difference.

We can define Primary key on only one column in a table.
But for Unique key there is no limit. we can define as many columns as we want.

THis is totally wrong Primary key too can contain multiple column.

Dear Anil,

we can achieve PK functionality via using Unique key+ Not Null contsraint

but it's not gud practice, when u can achieve same functionality via single PK constraint

  Was this answer useful?  Yes

Dumpo

  • Sep 23rd, 2007
 

It is also required to create a referential integrity (foreign key) constraint.

  Was this answer useful?  Yes

Sachin Agarwal

  • Jan 16th, 2008
 

The difference is as follows:
1) Basically we define a column/multiple column's as primary key that represents the whole row, A unique key can be any.
2) Primary key are considered as Clustered Index for the table and as we can define only one clustered index for the table, the other columns can only be non-clustered index.
3) We can use primary key in where clause for updating a record as it represents that row which may not be true in case of unique key. Even if the key is unique the table might have a composite key as a primary key.
4) A primary key can act as a foreign key for other related tables, a unique key cannot.
5) The data in a row is dependent on a primary key whereas it may not be for the unique key.

This is all that i know about it. Others can also add some more things to it.

ramesh_bollapragada

  • May 15th, 2008
 

hi every one

differences

1) primary key is defined only on one column where as unique + not null can be defined on many columns

2) we can define the relationship ( master detail relationship) with references to unique+not null column but it is not possible to remove the relationship with

alter table <tablename> primary key cascade. Because Unique + Not null is not exactly primary key

3) When you refer the data dictionery , with unique and not null it is going to create to different constraints where as with primary key it is going to create one constraint.


Regards

Ramesh B

  Was this answer useful?  Yes

mohanban

  • Jul 23rd, 2008
 

In addition to all this,unique key along with Not NULL constraint has got a name i.e. "candidate key".So a table having a column which is unique as well as not null apart from primar key is known as a candidate key.Thanks...

  Was this answer useful?  Yes

imtiazali

  • Jun 5th, 2009
 

First of all I will tell Not null, unique, primary key are constraints.

Not null:
When we are inserting record in the table, if we insert null values in the table then it should not accept the record for that purpose, we have to use not null constraint.
Unique: It is used to insert unique records. It does not accept duplicate keys.

Primary key: This is combination of both not null and unique constraints.
It does not accept both duplicate keys and null values.

  Was this answer useful?  Yes

abhi_only12000

  • Dec 21st, 2009
 

Primary key is a key assigned to any table to enforce uniqueness in the column value. The primary key constraint takes by default not null constraint. Unique key is the key which enforces only uniqueness but it allows the null value in the column.the basic difference in the primary key and the unique key with not null are as follows:

Primary key need not to enforce the not null.it by defult takes the not null value but unique key needs to assign not null in order to avoid the null value in the column.unique key allows the value in the column.

Only one primary key is allowed in the table either it is on the column basis or table basis. table basis primary key is the uniqueness on the basis of combination of columns. while more than one unique key can be there in the table.

Primary key can be used for referential integrity while unique key can be used for data uniqueness and consistency.

  Was this answer useful?  Yes

Balakrishna12207

  • Feb 7th, 2011
 

Primarykey is a combination of Unique Key and NotNull constraints.

The major difference is dependence upon the query performance.
One table can have only one Primarykey, But One table can have meny combination of Unique key and NotNull constraints.

Combination of Unique key and NotNull constraints are working as same as Primarykey constraint. But in Replication(DBA side) It does't work like Primary key.

Advantage is in column level also, we have meny primary key behaviours through Unique key and NotNull.

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions