Menu
Can anyone tell me what is the difference between a primary key and index key. And when to use which?
882
The primary index is created automatically when the table is created in the database. Primary key is mandatory.it avoid the duplicate of data. for ex (student rollno, material no, employee id)it should be a unique. when you create the foreign key in the particular table before it should be one primary key.
PRIMARY key is for identifying rows , prevent from inserting two rows with the same identical data... INDEX key is for accelerating searching ( minimal slow in insertion ) . UNIQUE key for unique data in a column .
An index key is a column, an ordered collection of columns, or an expression on which you define an index. Db2 uses an index key to determine the order of index entries. Good candidates for index keys are columns or expressions that you use frequently in operations that select, join, group, and order data.
A primary key is a special kind of index in that:
You tend to use the primary key as the most natural unique identifier for a row (such as social security number, employee ID and so forth, although there is a school of thought that you should always use an artificial surrogate key for this).
Indexes, on the other hand, can be used for fast retrieval based on other columns. For example, an employee database may have your employee number as the primary key but it may also have an index on your last name or your department.
Both of these indexes (last name and department) would disallow NULLs (probably) and allow duplicates (almost certainly), and they would be useful to speed up queries looking for anyone with (for example) the last name 'Corleone' or working in the 'HitMan' department.
183
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
