CODEWITHSUNDEEP
mysql
Behnam Rasooli
Behnam Rasooli

Reputation: 722

(MySQL) Why ID column should be set to Primary Key (being unique)?

I've heard Primary Key means to be unique. Correct me please if I'm wrong.

Assume we have a table of users. It has 3 columns of id, username and password. We usually set the id to be AUTO_INCREMENT. So it would technically make a new unique id each time we add a row to the table. Then, why we also set the id column to be Primary Key or Unique?

Upvotes: 0

Views: 473

Answers (3)

Thilo
Thilo

Reputation: 262514

A primary key does two things:

  1. enforce database integrity (uniqueness and not-null of the column)

  2. create an index to implement that, which also makes for fast look-up by the primary key column as a "side-effect".

You may not strictly need (1) if you can ensure that in your application code (for example by only using the auto-increment value), but it does not hurt.

You almost certainly want (2), though.

So it would technically make a new unique id each time we add a row to the table

Well, that is up to you. The unique id only gets inserted if you don't specify an explicit value. And technically, it is not guaranteed to be unique, it is just an auto-increment that does not take into consideration any existing values in the table (that may have somehow ended up in there).

Upvotes: 1

Explosion Pills
Explosion Pills

Reputation: 191749

AUTO_INCREMENT behavior only manifests when the column is not specified during an insert. Consider:

CREATE TABLE ai (
    ai int unsigned not null auto_increment,
    oi int unsigned,
    key (ai),
    primary key (oi)
);
INSERT INTO ai VALUES (1,2);
INSERT INTO ai VALUES (1,3);
INSERT INTO ai VALUES (null,5);

This will yield (1,2), (1,3), (2,5). Note how the AUTO_INCREMENT column has a duplicate.

Upvotes: 1

Mike Brant
Mike Brant

Reputation: 71384

Having a column as a key offers other aspects. First, if it is primary or unique, this would enforce that no query could enter a duplicate value for that key. Also keys can allow you do things like

INSERT ... ON DUPLICATE KEY UPDATE...

Of course you also want an index on the column for quick lookups.

Upvotes: 1

Related Questions