What is the difference between primary key, unique key, surrogate key?

  • Interview Candidate
  • 58
  • 37780

Editorial / Best Answer

Kolta Sam  

  • Member Since Jul-2011 | Jul 10th, 2011

Primary Key:

It is a visible key
It generated by user or application.
It could be changed by the user or application.
It could be queried
It used to form a relation between tables
It shouldn’t contain null value
It resemble table row
It is a unique identifier for a table object.
It contains only one key
It could contain numeric and strings characters.
It is an unique key which each row contain a distinct different key.
Example for it is a customer_Id.
It always starts by number one and second is two and so on but can starts with a different number.
Could created on one or more columns
No duplicate records

Secondary Key:

It used to form a relation between tables.
It is alternate table key.
It used to search data with primary key
It could contains null value
It could contains more than one secondary key for each table
Created only on one columns
No duplicate records
It creates index clustered by default


Surrogate Key:

It is invisible key for the user or the application.
It resembles database entity.
It generated by the system so it is invisible for user and application.
It shouldn’t contain null values
Only one surrogate key for each data entity
Its value is unique system wide.
Its value is never manipulated by the user or the application.
It never reused
It is frequently sequential number
It called synthetic key, an entity identifier, a system-generated key, a database sequence number, a factless key, a technical key, or an arbitrary unique identifier
No duplicate records

Showing Answers 1 - 58 of 58 Answers

nagaa

  • Aug 16th, 2005
 

what is the difference b/w rownum and rowid?

Nimi

  • Aug 25th, 2005
 

To answer your question its sufficient enough to understand their definitions. 
 
Primary Key: A column in a table whose values uniquely identify the rows in the table. A primary key value cannot be NULL. 
 
Unique Key: Unique Keys are used to uniquely identify each row in an Oracle table. There can be one and only one row for each unique key value. 
 
Surrogate Key: A system generated key with no business value. Usually implemented with database generated sequences.

Archit Dave

  • Aug 26th, 2005
 

i have one question is that 
 
what are the differences between primary key and unique key

vandita

  • Aug 27th, 2005
 

we can refer the primary key value in some other table ie child table.

  Was this answer useful?  Yes

suresh

  • Aug 27th, 2005
 

primaruy key should not dontain nulls and 
unique key can contains null values

Vijay Raj Jaiswal

  • Sep 20th, 2005
 

Primay Key                     Unique key

-----------                  ------------------

1.There is only one         there may be more than 1
Primary key for           Unique Key in table
1 table

 2.It can contain           It Can contain Null Value
Null value

gomathi.e

  • Sep 30th, 2005
 

unique key - allowes NULL ValuesPrimary Key - not allowed The NULL values

chandrakant

  • Oct 16th, 2005
 

nagaa Wrote: what is the difference b/w rownum and rowid?

what is the difference between primary key, unique key, sorrougate key?

3how

PSRaja

  • Oct 18th, 2005
 

send me the differences bet unique key & primary key

santhi

  • Nov 23rd, 2005
 

both primary key and unique key doesn't have duplicate values. but there will be only primary key in a table and u can have any number of unique keys.

Rajesh Kashyap

  • Dec 27th, 2005
 

Primery Key:- A column or set of columns that uniquely identify all the rows in a table. Primary keys do not allow null values. No two rows can have the same primary key value; therefore, a primary key value always uniquely identifies a single row.

Unique Key:- Unique Keys are used to uniquely identify each row in an Oracle table. There can be one and only one  row for each unique key value. no value repeate in the other rows of the same table.

Surrogate key:- An ID column populated via a sequence is known as 
a surrogate key. if the table has a true primary key

 

  Was this answer useful?  Yes

KOTESWARA RAO CHAVA

  • Apr 15th, 2006
 

 
primary key : duplicate recrods will not allowe,
                   null values also will not consider,
                   index automatically created on a column which u set a primary  key column,   

  Was this answer useful?  Yes

KOTESWARA RAO CHAVA

  • Apr 15th, 2006
 

 
primary key : duplicate recrods will not allowe,
                   null values also will not consider,
                   index automatically created on a column which u set  a        primary       key column,   
 
 
unique   key : no repeated twise a value in a  columns along with zero
 
 

  Was this answer useful?  Yes

rajanipriya

  • May 9th, 2006
 

 Aactually, the rows of a table  in database terminology are considered as candidates of a table. So, the colunm that uniquely identifies these candidates of a table is called CAndidate Key.

Candidate key shortly called as key ---->> The column which is used to Uniquely identify the rows of a table.

 Primary key ----->>  Unique Key +Not Null contraint

 Unique Key  ----->>  The key column which has no repeated values in it.

  Was this answer useful?  Yes

yogesh _ gyanendra

  • Dec 13th, 2006
 

Hi dear,primary key can't contain NULL but Unique key contain NULL value..........

  Was this answer useful?  Yes

shilpa ashwin

  • Feb 8th, 2007
 

ROWID : Unique index number of every row of a table maintained by the database automatically



ROWNUM: Sequential number of rows in the resultset object.

  Was this answer useful?  Yes

praveenladdu1

  • Mar 7th, 2007
 

hi
this is correct
u people r saying wt is primary and wt is unique key
but the diffrence is

 unique key contains only unique valvues i.e it does not allow duplicate values
and
where as a primary key allows only unique values and also it have 
NOT NULL charactaristic too ..

  Was this answer useful?  Yes

Rohan Kumar K

  • Mar 26th, 2007
 

Primary key: key + not null values in the table.
Unique key : key + null /not null  values in the table.

  Was this answer useful?  Yes

Yogita

  • Apr 18th, 2007
 

Primary Key:
1) It creates clustered index by default
2) It doesn't allow nulls


Unique Key:
1) It creates non-clustered index by default
2) It allows only one null value

  Was this answer useful?  Yes

AMIYA KUMAR SAHOO

  • May 2nd, 2007
 

The Primary key never contains any Null value

P.V.Sarveswararao

  • May 25th, 2007
 

Unique key can contain null values
Primary key can't contain null values Primary key is a combnation of Unique and notnull.

Srinivas N

  • Jun 27th, 2007
 

Primary Key :
Can be only one per table
Can be created on one or more than one column
Will not allow null values
Can be reffered in another table i.e primary key and foreign key relation

Unique Key: It allows null values
If values exists it should be unique

bhargav

  • Jul 18th, 2007
 

Primary key is a combination of unique key and not null
 

vikrant

  • Jul 21st, 2007
 

Primary key is having both the constraints that are unique key and not null. There can be only one primary key in a table. But unique key is a key which uniquely identifies each row in a table but its not mandatory that it should be primary key but a primary key has to be unique as well as not null key. There can be more than one unique key in a table.

  Was this answer useful?  Yes

Amit Aggarwal

  • Aug 11th, 2007
 

Primary key has a unique value it doesn't contains Null value but in the unique key it takes null value also but only once time.

  Was this answer useful?  Yes

Prakash Chandra Das

  • Aug 18th, 2007
 

Primary Key will never contain Null Value. Primary Key is a key is always NOT NULL

  Was this answer useful?  Yes

kamalalkarim

  • Aug 30th, 2007
 

Well the basic difference b/w primary key and the unique key is...

Primary key not only keeping the unique record in the particular field also it sorts the records as well and by this key we can relate other tables by a creating a referential key.

unique key only act in a column to keep the non-duplicate records.

I hope it would clear the idea about these two keys.

Regards,

  Was this answer useful?  Yes

swaminathan

  • Sep 14th, 2007
 

Unique Key - If a column is defined as UNIQUEY KEY, it only accept Non-Duplicate value. But The column can contain a null value.

Primary Key - Primary key combines UNIQUE + NOTNULL

If a column is defined as primary key, the column can accept  NOT null and unique value only. 

  Was this answer useful?  Yes

prasads_delight1

  • Sep 18th, 2007
 

       The field which is bounded by the Primary key constraint should not accept null valus and duplicate valus. And the primary key constraint field of a table will give the provision to relate with another table in the database which contains the field with same datatype. 
       More than one primary key fields are possible in a single table, but strictly depends upon the nature of the data and the relations among the fields in the table.

  Was this answer useful?  Yes

harshada

  • Sep 30th, 2007
 

unique key is a key which is only one key while primary key is a key which is only unique

  Was this answer useful?  Yes

sridhar

  • Oct 4th, 2007
 

Both are pseudo columns, one thing is, rownum will create on a fly while displaying resultset whereas rowid is the address of the record for the entire database.

  Was this answer useful?  Yes

purnimamca

  • Nov 5th, 2007
 

difference between primary key an dunique key is by default clustered index is created in primary where as a non clustered index is created in unique key and it alsoe enforces uniqueness of the column

  Was this answer useful?  Yes

Sunil Raina

  • Nov 26th, 2007
 

Primary key creates a clustered index and unique key creates nonclustered index

  Was this answer useful?  Yes

Reddy555

  • May 24th, 2008
 

Primary key does not allow nulls and repeated values
where as unique key accepts nulls but in unique key data should not repeated

  Was this answer useful?  Yes

navin05_06

  • Jun 2nd, 2008
 

Primary Key allows not null values and unique values

Unique key allows null and unique values

Only  Primary Key is allowed for a table but Multiple unique key can exist in a table

  Was this answer useful?  Yes

pradhanmk1

  • Jun 10th, 2008
 

Primary Key: A column in a table whose values uniquely identify the rows in the table.There can be only one primary key in a table. A primary key value cannot be NULL. 
 
Unique Key: Unique Keys are used to uniquely identify each row in an Oracle table. There can be one or more unique key in a table.
 
Surrogate Key: A system generated key with no business value. Usually implemented with database generated sequences.

  Was this answer useful?  Yes

Rajanikanththota

  • Jul 23rd, 2008
 

A UNIQUE constraint is similar to PRIMARY key, but you can have more than one UNIQUE constraint per table.

When you declare a UNIQUE constraint, SQL Server creates a UNIQUE index to speed up the process of searching for duplicates. In this case the index defaults to NONCLUSTERED index, because you can have only one CLUSTERED index per table.

* The number of UNIQUE constraints per table is limited by the number of indexes on the table i.e 249 NONCLUSTERED index and one possible CLUSTERED index.

Contrary to PRIMARY key UNIQUE constraints can accept NULL but just once. If the constraint is defined in a combination of fields, then every field can accept NULL and can have some values on them, as long as the combination values is unique.

  Was this answer useful?  Yes

arvindgeek

  • Aug 7th, 2008
 

We can have foreign key refer to Unique Key (UK) and also to Primary Key (which is combination of UK+not null); then it means we can create primary key equivalent constratint on a column with the help of UK and Not Null, then why do we have PK?

  Was this answer useful?  Yes

santoshkharma

  • Aug 15th, 2008
 

Primary Key:
1) One Primary Key per Table
2) By Default creates Clustred Index
3) Cannot Have Null values.

Unique Key
1) Can have multiple Unique keys.
2) By default creates Non-Clustred Index.
3) Can Have one Null value.

Shoot a question on MSSql concepts.
Thanks and Cheers!!!

prashantmirje2000

  • Feb 25th, 2009
 

primary key                        unique key

can't NULL                         can NULL

By default create               By default create 
cluster index                      non cluster index
 

  Was this answer useful?  Yes

suman thummala

  • Jun 30th, 2009
 


rownum is a pseudo column which is generated for query data set at runtime. while rowid is the physical address of the row and hence definition suggest rowid for a row will never change but row num will always change.

Rowid has a physical significance i.e you can read a row if you know rowid. It is complete physical address of a row.

While rownum is temporary serial number allocated to each returned row during query execution.

Primary Key is Unique and not null
Unique Key is Unique and can contain null values.

  Was this answer useful?  Yes

imnrj23

  • Nov 11th, 2009
 

Primary Key & Unique Key

1.)))
P_key - A column in a table whose values uniquely identify the 

rows/tuple/entry of the table. A primary key value cannot be NULL.
U_key - A column in a table whose values uniquely identify the 
rows/tuple/entry of the table. A unique key value can be NULL.
2.)))
P_key - A table can have only 1 primary key (If its on multiple columns, its a composite key).
U_key - A table can have 1 or more Unique keys.

3.)))
P_key - Clustered index is created on Primary key constraint
U_key - non clustered unique indexes iscreated on Unique key constraint.

Surrogate Key: A system generated key with no business value. Usually implemented with database generated sequences.

Surrogate Key features:
  • the value is unique system-wide, hence never reused;
  • the value is system generated;
  • the value is not manipulable by the user or application;
  • the value contains no semantic meaning;
  • the value is not visible to the user or application;
  • the value is not composed of several values from different domains.

Raza Darvaish

  • Apr 1st, 2011
 

A primary key doesn't allow nulls, but a unique key can allow ONE null.

A primary key can be a foreign key in other relation while a unique key cannot be referenced.

  Was this answer useful?  Yes

rahulksinghh

  • Jul 7th, 2011
 

difference between rowid and rownum


you can check rowid and rownum for every table by using syntax

select rownum,rowid from tablename;

when you create a table than rownum and rowid automatically created by database like here i created a table fee,than by using syntax select rowid,rownum,fid,feeammount,student_id from fee;
we got the table which have rowid and rownum......
    ROWNUM ROWID              FID        FEEAMMOUNT STUDENT_ID
---------- ------------------ ---------- ---------- ------------
         1 AAAEfwAABAAAKxCAAA f01             56000 1ms08is088
         2 AAAEfwAABAAAKxCAAB f02            450000 1ms08is088
         3 AAAEfwAABAAAKxCAAC f03             45000 1ms08is090

so as we can see rownum is the number for each record given by database.....and rowid is a id given by database for each record...

  Was this answer useful?  Yes

Kolta Sam

  • Jul 10th, 2011
 

Primary Key:

It is a visible key
It generated by user or application.
It could be changed by the user or application.
It could be queried
It used to form a relation between tables
It shouldn’t contain null value
It resemble table row
It is a unique identifier for a table object.
It contains only one key
It could contain numeric and strings characters.
It is an unique key which each row contain a distinct different key.
Example for it is a customer_Id.
It always starts by number one and second is two and so on but can starts with a different number.
Could created on one or more columns
No duplicate records

Secondary Key:

It used to form a relation between tables.
It is alternate table key.
It used to search data with primary key
It could contains null value
It could contains more than one secondary key for each table
Created only on one columns
No duplicate records
It creates index clustered by default


Surrogate Key:

It is invisible key for the user or the application.
It resembles database entity.
It generated by the system so it is invisible for user and application.
It shouldn’t contain null values
Only one surrogate key for each data entity
Its value is unique system wide.
Its value is never manipulated by the user or the application.
It never reused
It is frequently sequential number
It called synthetic key, an entity identifier, a system-generated key, a database sequence number, a factless key, a technical key, or an arbitrary unique identifier
No duplicate records

  Was this answer useful?  Yes

nandha

  • Aug 17th, 2011
 

Primary key- It is unique identifier,it will not accept null values,

Unique- it is unique identifier it is accept null values..

  Was this answer useful?  Yes

Gopinath

  • Aug 17th, 2011
 

Primary key is the key can have unique value and non null records. where as unique key can have unique value but it allow only one null value.

  Was this answer useful?  Yes

arjun

  • Sep 1st, 2011
 

Primary key: unique+notnull

It is implicitly indexed.


Unique key accepts null and non-unique records,

No indexing is done automatically.


  Was this answer useful?  Yes

rama

  • Sep 5th, 2011
 

primary key is not null +unique
unique key is null or not null +unique

  Was this answer useful?  Yes

Rajakumar B V

  • Sep 11th, 2011
 

Primary key
1) It creates clustered index by default
2) It doesn't allow nulls

Unique Key:
1) It creates non-clustered index by default
2) It allows only one null value

  Was this answer useful?  Yes

Lucky

  • Nov 29th, 2011
 

We can create only one primary key per table where as we can create numbers of unique key in a table.

Primary key does not accept any null value but unique key accepts maximum of one null value.

Primary key creates clustered index by default where unique key creates non-clustered index by default.

  Was this answer useful?  Yes

Faisal zaman Gandapure

  • Jan 26th, 2014
 

there is a minor difference between them..
Primary Key :
This is a unique key .
it does not allow null values.
Unique Key:
This key is also unique .
And it allow null values and but only one.

  Was this answer useful?  Yes

Kote

  • Mar 26th, 2014
 

Primary key does not accept any null value but unique key accepts null value for each row of Unique columns. Kote

  Was this answer useful?  Yes

gagandeepp

  • Aug 21st, 2014
 

null values can be used more than one times in the unique key column .....because null is not equal to null or null is not equal to zero or null is not equal to space..............

  Was this answer useful?  Yes

sumalatha

  • Dec 23rd, 2016
 

Primary Key=Unique+Not null
It means primary key does not allow null values.

Unique key allows null values.

  Was this answer useful?  Yes

Sukdya

  • Nov 27th, 2017
 

Primary key doest not allow null values but unique key allows only one null value.

  Was this answer useful?  Yes

gopi

  • May 18th, 2018
 

primary key not allow null values where as unique allows but both are alternates.
surrogate key serve as primery key in type 2 dimension tables

  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