Odtnhj

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?

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.

You understand it correctly, but they are doing different things.

PRIMARY KEY with AUTO_INCREMENT means we want this column isn't duplicate on the value and it will be auto increase if we didn't set the value.

but how about we only set AUTO_INCREMENT it only means it will be auto increase if we didn't set the value. but didn't make sure the value is unique.

AUTO_INCREMENT doesn't make the column uniqe. It only "automatically" fills a value when creating a row, if missing. But you can later update the values, or also create a row by explicitly providing the value.

PRIMARY KEY denies any modification sql statement that would cause 2 different entries storing equal values. So it guarantees you, that your DB is in a correct state.