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?
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.
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.
A primary key does two things:
enforce database integrity (uniqueness and not-null of the column)
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).
