0

Can anyone explain what are the difference between Primary,Unique and Index key of the Mysql . Can you explain the of each one in different situations. ?

A Paul
  • 8,113
  • 3
  • 31
  • 61
Vivek S
  • 2,010
  • 1
  • 13
  • 15

2 Answers2

2

Primary is the value of a column of a table or a combination of values of more than one columns of a table with which we can identify each row. For instance, let we have a table Customers with columns ID int, FirstName varchar(50), LastName(50). Then the ID column can be used to identify the users, so it can be used as the PK of this table. There would be only one customer, if there exist at all, with ID = 4 or with ID = 10 etc.

Unique is the value of a column of a table that for each separate record should have a unique value.

Index key is a column or a combination of columns of a table that make the execution of queries against the table to run faster. It is useful, when a table has many records and even a simple query takes too long time to be completed.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Christos
  • 53,228
  • 8
  • 76
  • 108
1

yes these are different

KEY or INDEX refers to a normal non-unique index. Non-distinct values for the index are allowed, so the index may contain rows with identical values in all columns of the index. These indexes don't enforce any restraints on your data so they are used only for making sure certain queries can run quickly.

UNIQUE refers to an index where all rows of the index must be unique. That is, the same row may not have identical non-NULL values for all columns in this index as another row. As well as being used to speed up queries, UNIQUE indexes can be used to enforce restraints on data, because the database system does not allow this distinct values rule to be broken when inserting or updating data.

Your database system may allow a UNIQUE index to be applied to columns which allow NULL values, in which case two rows are allowed to be identical if they both contain a NULL value (the rationale here is that NULL is considered not equal to itself). Depending on your application, however, you may find this undesirable: if you wish to prevent this, you should disallow NULL values in the relevant columns.

PRIMARY acts exactly like a UNIQUE index, except that it is always named 'PRIMARY', and there may be only one on a table (and there should always be one; though some database systems don't enforce this). A PRIMARY index is intended as a primary means to uniquely identify any row in the table, so unlike UNIQUE it should not be used on any columns which allow NULL values. Your PRIMARY index should be on the smallest number of columns that are sufficient to uniquely identify a row. Often, this is just one column containing a unique auto-incremented number, but if there is anything else that can uniquely identify a row, such as "countrycode" in a list of countries, you can use that instead.

Some database systems (such as MySQL's InnoDB) will store a table's records on disk in the order in which they appear in the PRIMARY index.

uvais
  • 416
  • 2
  • 6