Search code examples
mysqlsqlprimary-keyauto-increment

What's the point of using AUTO_INCREMENT and PRIMARY KEY at the same time in MySQL?

In MySQL, I understand below

AUTO_INCREMENT: used to make INT value to increase automatically every time a user creates a row.

PRIMARY KEY: used to make value unique in that table.

However, I oftentimes see them used together when defining id.

I don't understand the point of using AUTO_INCREMENT with PRIMARY KEY since AUTO_INCREMENT itself would make id unique.

Is there any reason they are used together when creating id?

Solution

  • The primary key has three properties:

    1. It is unique.
    2. It is non-null.
    3. There is only one per table.

    Defining the key as a primary key means that it should also be used for foreign key references.

    In addition, MySQL clusters the data by the primary key. So the declaration instructs new rows to go at the "end" of the table -- meaning adjacent to the most recent inserts on the data pages.

    In addition, duplicate values for the auto-incremented id could be created in various ways. One way is that the increment counter can be reset, causing duplicates. MySQL should be pretty thread-safe on duplicates for concurrent updates, but bugs have been reported. As a primary key, no duplicates will be allowed into the table.