MySQL - What is the correct primary key method

Question

I am starting to develop an application using MySQL, and although I developed the application before using the databases, I usually switched to the incrementing id method. The other day, I read somewhere the wiring and saw that someone was chosen for this, because it incorrectly "normalizes the database." I am not a big database person, but I wanted to make sure that I was doing it right.

Any ideas, help / guidance?

Answer 1

There are two main methods for choosing a primary key :

• Selection of a primary key from existing keys of a natural candidate .
• . , . , .

, .

, , .

Answer 2

drop table if exists users;
create table users(
 user_id int unsigned not null auto_increment primary key, -- clustered auto_inc PK
 username varbinary(32) not null,
 unique key users_username_idx(username)
)engine=innodb;

insert into users (username) values ('f00'),('bar'),('bish'),('bash'),('bosh'),('F00');

drop table if exists user_friends;
create table user_friends(
 user_id int unsigned not null,
 friend_user_id int unsigned not null,
 primary key (user_id, friend_user_id) -- clustered composite PK
)engine=innodb;

insert into user_friends values
(1,2),(1,3),  (2,1),(2,5),  (3,5),  (4,1),(4,2),(4,3),(4,5),(4,6),  (5,4),(5,1);

-, (user_id, friend_user_id)

select * from user_friends where user_id = 4; -- uses PK index

, PK, user_id friend_user_id, - . .

delete from user_friends where user_id = 4 and user_friend_id = 5; -- uses PK index

( )

drop table if exists user_friends;
create table user_friends(
 friend_id int unsigned not null auto_increment primary key, -- clustered auto_inc PK
 user_id int unsigned not null,
 friend_user_id int unsigned not null,
 unique key user_friends_idx (user_id, friend_user_id) -- non clustered secondary index
)engine=innodb;

-, , index on (user_id, friend_user_id). 2 PK , .

. / 2 .cc.

select * from user_friends where user_id = 4; -- uses secondary index not PK

, , friend_id PK, , 1 , PK.

delete from user_friends where friend_id = 10; -- uses PK index

, , , , :)

Answer 3

id . . , auto incrementing id - , .

Answer 4

Auto-incrementing identifiers and normalization are not related (i.e. you can use auto-incrementing identifiers in a non-normalized database or have a normalized database that does not use auto-incrementing identifiers).