All Forums UDA
daggula 5 posts Joined 02/11
17 Feb 2011
Difference between key and index?

Can any one tell me what is the difference between key and index?

what is the difference between primary key and unique primary key?

Thanks
Ashok Reddy Daggula

Dixxie 58 posts Joined 12/10
17 Feb 2011

Hi,
Basically the Primary Key is a Logical Concept for maintain the uniquines of a row in a Entity (by example the Customer Id in the Customers Table), this always is UNIQUE.

The Primary Index is a Physical concept for the rows distribution about the AMPs, this must match with the PK or not, the PI must prove an uniform distribution of rows over all AMPs.

BR.

sudheer51 7 posts Joined 03/13
26 Nov 2013

Primary KEY is more of a logical thing, however Primary INDEX is more of physical thing.
 
In Teradata,
Primary INDEX is used for finding best access path for data retrieval and data insertion and
Primary KEY is used for finding each rows uniquely just like in other RDBMS.

Primary KEY is more of a logical thing however Primary INDEX is more of physical thing. In Teradata, Primary INDEX is used for finding best access path for data retrieval and data insertion and Primary KEY is used for finding each rows uniquely just like in other RDBMS. - See more at: http://usefulfreetips.com/Teradata-SQL-Tutorial/difference-between-teradata-primary-index-and-primary-key/#sthash.Fudc7paG.dpuf

Primary KEY is more of a logical thing however Primary INDEX is more of physical thing. In Teradata, Primary INDEX is used for finding best access path for data retrieval and data insertion and Primary KEY is used for finding each rows uniquely just like in other RDBMS. - See more at: http://usefulfreetips.com/Teradata-SQL-Tutorial/difference-between-teradata-primary-index-and-primary-key/#sthash.Fudc7paG.dpuf

If at first you don't succeed; call it version 1.0

Kawish_Siddiqui 37 posts Joined 03/07
02 Dec 2013

Here are some differences.

 

• A PK is a relational modeling convention which allows each row to be uniquely identified.

• A PI is a Teradata convention which determines how the row will be stored and accessed.

• A significant percentage of tables may use the same columns for both the PK and the PI.

• A well-designed database will use a PI that is different from the PK for some tables.

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

Primary Key                         

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

  • Logical concept of data modeling    
  • Teradata doesn’t need to recognize  
  • No limit on number of columns       
  • Documented in data model (Optional in CREATE TABLE)         
  • Must be unique                      
  • Identifies each row                 
  • Values should not change            
  • May not be NULL – requires a value  
  • Does not imply an access path       
  • Chosen for logical correctness      

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

 Primary Index

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

  • Physical mechanism for access and storage
  • Each table can have (at most) one primary index
  • 64 column limit
  • Defined in CREATE TABLE statement
  • May be unique or non-unique
  • Identifies 1 (UPI) or multiple rows (NUPI)
  • Values may be changed (Delete + Insert)
  • May be NULL
  • Defines most efficient access path
  • Chosen for physical performance

 

Kawish Siddiqui -

Raja_KT 1246 posts Joined 07/09
02 Dec 2013

As per your question: There is no such thing as unique primary key.
Primary key is unique and not null.
Yes, for those coming from other DB background, like oracle, the concept is  different. 
Cheers,
Raja

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

You must sign in to leave a comment.