MySQL – Unique and Primary Keys in Databases: Best Practices

databaseMySQLoptimizationweb-development

I'm having quite some doubts here… If I have a table related to username/password login how can I assure there won't be any more matches of the same username and password in the same table using sql? For example:

>username: username
>password: password

^this user just signed up. So the following conditions apply:

1º there can't be any more users named "username"

2º most surely there can't be any more users named "username" AND with the same password

so how exactly should I put the keynames in the database? The id is primary, should I put username AND password as unique to form an unique pair and avoid another identical insertion?

Best Answer

Usernames have to be unique, they identify a user. Passwords do not. Passwords have to be kept secret anyway. You can't tell a user "Hey, choose another password; another user is using this one already". If he is a hacker, he will love that!

You should not store the passwords anyway. Instead store their hashes. See Salted Password Hashing - Doing it Right.

If you have two columns that have to be unique, either their combination must be unique or they must be unique independently.

An example of a unique combination would be monthly data indentified by year and month stored in two separate columns. Here it would make sense to use these two columns as primary key (PK). If you already have another PK, create one unique index or unique constraint on these two columns. See also When should I use a unique constraint instead of a unique index?

An example of two independently unique columns would be a username and a nickname. The username is used for logins; the nickname is displayed to other users. Here I would use the username as PK and create a unique index on the nickname.

The PK is the primary means of identifying a record. The PK is also referenced in foreign key constraints when defining 1 to n or 1 to 1 relationships between tables. It is a good practice not to choose a column whose value can change over time. If users were allowed to change their username, as an example, choose an auto-increment column as PK and create a unique index on the username.

Related Topic