Difference between key and index?

UDA
Enthusiast

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
Tags (3)
4 REPLIES
Enthusiast

Re: Difference between key and index?

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.
Enthusiast

Re: Difference between key and index?

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-primar...

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-primar...

Enthusiast

Re: Difference between key and index?

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

Enthusiast

Re: Difference between key and index?

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