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?

  • 2
    Maybe I misunderstand something, but doesn't the first constraint imply the second? – user7043 Jan 1 '14 at 17:35
  • @delnan It does indeed, but my point here is how can I assure there won't be identical rows on basis referring to two different fields in that same row. Imagine It wasn't about username and passwords but instead it was about credit cards and value inside the bank account. To each credit card there can only be one unique value, vice-versa, and this pair can only exist one time in a table. How can I assure this happens? – user111671 Jan 1 '14 at 17:47
  • 3
    Values must be unique in a bank account? If I have 100$ on my account no one else is allowed to have 100$ on his account? – Olivier Jacot-Descombes Jan 1 '14 at 17:53
  • If you're asking what I think you're asking, you might be better using "username" and "email address" as your examples, as they are more likely to be fields you want to be unique. In your first example, you probably wouldn't match passwords even if they were the same, if you were hashing them properly. In the second, that would mean two accounts couldn't have the same balance: also probably not what you're looking for. – GeminiDomino Jan 1 '14 at 17:54
  • If you want to make sure that two distinct fields are unique, you can create a unique index on each field. Then neither value will be able to be duplicated in another row. If you want two fields to be unique as a pair, then you can create a unique index using BOTH fields. Technically, that is how you can do it, but in this case, I agree with the other commenters/answer: I really don't think this is what you want to do. – GeminiDomino Jan 1 '14 at 17:57

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.

  • @OliverJacot-Descombes I know of this, ty :P I'm md5'ing them. Anyway, if you could check the comment I made on the top and try to explain to me how exactly unique and primary keys should be used in an example such as the one above I would be very thankful! :) – user111671 Jan 1 '14 at 17:52
  • 2
    Please google on that md5 and use something better than that like sha1 of better: crypt: php.net/manual/en/faq.passwords.php on part: "Why are common hashing functions such as md5() and sha1() unsuitable for passwords?" – Luc Franken Jan 3 '14 at 9:44
  • @LucFranken: Look at the comments that follow the article. This statement seems to wrong. – Olivier Jacot-Descombes Jan 21 '14 at 21:43
  • Yes there are multiple ways but the advised way for new implementations is the one noticed. Since the new methods: php.net/manual/en/function.password-hash.php there is even flexibility to upgrade the hashing algorithms resulting in better code. It is correct that pure in essence also sha1 for example might suit needs but the full solution of PHP is stronger and there are less coding issues possible because of the salting methods included. Developing your own security systems is always a great risk. – Luc Franken Jan 22 '14 at 9:21

Add a unique constraint on the username column. Any password logic should go in the application code, but best to just check that the passwords are strong enough.


I think the better implementation is to have the usernames to be unique, but the passwords may have duplications. It's also the common practice in most web-apps. You can set the username as the primary key in your table

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.