GeekInterview.com
Series: Subject: Topic:
Question: 377 of 378

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

Asked by: Interview Candidate | Asked on: Aug 27th, 2005

Editorial / Best Answer

Answered by: Kolta Sam

View all answers by Kolta Sam

Member Since Jul-2011 | Answered On : 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 - 55 of 55 Answers
nagaa

Answered On : Aug 16th, 2005

What is the difference b/w rownum and rowid?

Yes  1 User has rated as useful.
  
Login to rate this answer.
Nimi

Answered On : 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.

Yes  4 Users have rated as useful.
  
Login to rate this answer.
Archit Dave

Answered On : Aug 26th, 2005

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

Yes  1 User has rated as useful.
  
Login to rate this answer.
vandita

Answered On : Aug 27th, 2005

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

  
Login to rate this answer.
suresh

Answered On : Aug 27th, 2005

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

Yes  3 Users have rated as useful.
  
Login to rate this answer.
Vijay Raj Jaiswal

Answered On : Sep 20th, 2005

Primay Key                     Unique key-----------                  ------------------1.There is only one         there may be more than 1Primary key for           Unique Key in table 1 table 2.It can contain           It Can contain Null ValueNull value

Yes  1 User has rated as useful.
  
Login to rate this answer.
gomathi.e

Answered On : Sep 30th, 2005

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

Yes  2 Users have rated as useful.
  
Login to rate this answer.
chandrakant

Answered On : 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

Yes  1 User has rated as useful.
  
Login to rate this answer.
PSRaja

Answered On : Oct 18th, 2005

Send me the differences bet unique key & primary key

Yes  1 User has rated as useful.
  
Login to rate this answer.
santhi

Answered On : 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.

Yes  2 Users have rated as useful.
  
Login to rate this answer.
Rajesh Kashyap

Answered On : 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 

  
Login to rate this answer.
KOTESWARA RAO CHAVA

Answered On : 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,   

  
Login to rate this answer.
KOTESWARA RAO CHAVA

Answered On : 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  

  
Login to rate this answer.

 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.

  
Login to rate this answer.
yogesh _ gyanendra

Answered On : Dec 13th, 2006

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

  
Login to rate this answer.
shilpa ashwin

Answered On : 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.

  
Login to rate this answer.
praveenladdu1

Answered On : Mar 7th, 2007

View all answers by praveenladdu1

Hi this is correct u people r saying wt is primary and wt is unique keybut the diffrence is unique key contains only unique valvues i.e it does not allow duplicate valuesand where as a primary key allows only unique values and also it have NOT NULL charactaristic too ..

  
Login to rate this answer.
Rohan Kumar K

Answered On : Mar 26th, 2007

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

  
Login to rate this answer.
Yogita

Answered On : 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

  
Login to rate this answer.
AMIYA KUMAR SAHOO

Answered On : May 2nd, 2007

The Primary key never contains any Null value

Yes  1 User has rated as useful.
  
Login to rate this answer.

Primary key cannot contain null value but unique can contain a single null value or primary key have to be not null and unique key can null or not null.

Yes  1 User has rated as useful.
  
Login to rate this answer.
P.V.Sarveswararao

Answered On : 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.

Yes  1 User has rated as useful.
  
Login to rate this answer.
Srinivas N

Answered On : Jun 27th, 2007

Primary Key : Can be only one per tableCan be created on one or more than one columnWill not allow null valuesCan be reffered in another table i.e primary key and foreign key relationUnique Key: It allows null valuesIf values exists it should be unique

Yes  3 Users have rated as useful.
  
Login to rate this answer.
bhargav

Answered On : Jul 18th, 2007

Primary key is a combination of unique key and not null 

Yes  1 User has rated as useful.
  
Login to rate this answer.
vikrant

Answered On : 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.

  
Login to rate this answer.
Amit Aggarwal

Answered On : 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.

  
Login to rate this answer.
Prakash Chandra Das

Answered On : Aug 18th, 2007

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

  
Login to rate this answer.
kamalalkarim

Answered On : Aug 30th, 2007

View all answers by kamalalkarim

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,

  
Login to rate this answer.
swaminathan

Answered On : 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 + NOTNULLIf a column is defined as primary key, the column can accept  NOT null and unique value only. 

  
Login to rate this answer.

       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.

  
Login to rate this answer.
harshada

Answered On : Sep 30th, 2007

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

  
Login to rate this answer.
sridhar

Answered On : 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.

  
Login to rate this answer.
purnimamca

Answered On : Nov 5th, 2007

View all answers by purnimamca

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

  
Login to rate this answer.
Sunil Raina

Answered On : Nov 26th, 2007

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

  
Login to rate this answer.
Reddy555

Answered On : May 24th, 2008

View all answers by Reddy555

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

  
Login to rate this answer.
navin05_06

Answered On : Jun 2nd, 2008

View all answers by navin05_06

Primary Key allows not null values and unique valuesUnique key allows null and unique valuesOnly  Primary Key is allowed for a table but Multiple unique key can exist in a table

  
Login to rate this answer.
pradhanmk1

Answered On : Jun 10th, 2008

View all answers by pradhanmk1

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.

  
Login to rate this answer.

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.

  
Login to rate this answer.
arvindgeek

Answered On : Aug 7th, 2008

View all answers by arvindgeek

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?

  
Login to rate this answer.
santoshkharma

Answered On : Aug 15th, 2008

View all answers by santoshkharma

Primary Key: 1) One Primary Key per Table2) By Default creates Clustred Index3) Cannot Have Null values.Unique Key1) 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!!!

Yes  1 User has rated as useful.
  
Login to rate this answer.

Primary key                        unique keycan't NULL                         can NULLBy default create               By default create  cluster index                      non cluster index 

  
Login to rate this answer.
suman thummala

Answered On : Jun 30th, 2009

View all answers by suman thummala

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 nullUnique Key is Unique and can contain null values.

  
Login to rate this answer.
imnrj23

Answered On : Nov 11th, 2009

View all answers by imnrj23

Primary Key & Unique Key1.))) 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.

Yes  1 User has rated as useful.
  
Login to rate this answer.
Raza Darvaish

Answered On : Apr 1st, 2011

View all answers by Raza Darvaish

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.

  
Login to rate this answer.
rahulksinghh

Answered On : Jul 7th, 2011

View all answers by rahulksinghh

Difference between rowid and rownumyou can check rowid and rownum for every table by using syntaxselect 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 1ms08is090so 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...

  
Login to rate this answer.
Kolta Sam

Answered On : Jul 10th, 2011

View all answers by Kolta Sam

Primary Key:It is a visible keyIt 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 tablesIt shouldn’t contain null valueIt resemble table rowIt is a unique identifier for a table object. It contains only one keyIt 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 columnsNo duplicate recordsSecondary Key:It used to form a relation between tables.It is alternate table key.It used to search data with primary keyIt could contains null valueIt could contains more than one secondary key for each tableCreated only on one columnsNo duplicate recordsIt creates index clustered by defaultSurrogate 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 valuesOnly one surrogate key for each data entityIts value is unique system wide.Its value is never manipulated by the user or the application.It never reusedIt is frequently sequential numberIt called synthetic key, an entity identifier, a system-generated key, a database sequence number, a factless key, a technical key, or an arbitrary unique identifierNo duplicate records

  
Login to rate this answer.
nandha

Answered On : 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..

  
Login to rate this answer.
Gopinath

Answered On : 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.

  
Login to rate this answer.
arjun

Answered On : Sep 1st, 2011

Primary key: unique+notnull
It is implicitly indexed.
Unique key accepts null and non-unique records,
No indexing is done automatically.

  
Login to rate this answer.
rama

Answered On : Sep 5th, 2011

Primary key is not null +unique
unique key is null or not null +unique

  
Login to rate this answer.
Rajakumar B V

Answered On : 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

  
Login to rate this answer.
Lucky

Answered On : 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.

  
Login to rate this answer.
Faisal zaman Gandapure

Answered On : 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.

  
Login to rate this answer.
Kote

Answered On : Mar 26th, 2014

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

  
Login to rate this answer.
gagandeepp

Answered On : 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..............

  
Login to rate this answer.

Give your answer:

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

Related Open Questions

Ads

Connect

twitter fb Linkedin GPlus RSS

Ads

Interview Question

 Ask Interview Question?

 

Latest Questions

Interview & Career Tips

Get invaluable Interview and Career Tips delivered directly to your inbox. Get your news alert set up today, Once you confirm your Email subscription, you will be able to download Job Inteview Questions Ebook . Please contact me if you there is any issue with the download.