Entering duplicate data, what will happen?

Question

if by chance, two different person were to register an account and both choose the same username but the system does not allow identical username. what will happen? do we as backend programmer need to find ways to fix this or the php/mysql handle it themself?

Answer 1

Muhd Adam If your table does not have a UNIQUE constraint on the username column, then both accounts will be created as separate data rows.

This will cause issues if your queries expect a single row on username.

I recommend putting a unique constraint on the username column to avoid such issues. Then handle the exception so that a message can be returned to the user.

Answer 2

In that case I do not know what might happen. Possibly one of them is rejected because, in theory, MYSQL takes care of those cases. Here you can read much more about it:

https://dev.mysql.com/doc/refman/8.0/en/internal-locking.html

If you can change the structure of the table to add a UNIQUE or PRIMARY KEY identifier it would be great, since your table would have a much stronger structure.

Answer 3

It depends on the structure of the table. If the duplicate field is UNIQUE or PRIMARY KEY you will get an error (the way in which the error is generated may vary depending on the driver you use for the connection).

Ideally, in these cases, you should review the records in the table and verify that the values are not recorded in the table. If they are registered, you must notify the user that the registration failed and that they should try a different record.

Another alternative is to use INSERT IGNORE in your query, that way if the registry is already in the table MySQL will be responsible for not registering the data. Although this is inefficient, since you are not notifying the user that something went wrong, and you can affect their experience using your application.

Answer 4

if you have made the username column a unique colum then mysql will throw an error when the query tries to run.

Answer 5

for example, I have just bring my system online...so all the user that have registered with me manually are required to register again online, what if two user submitted the same username at the same time?

answering the above question:
1. The username is not a primary or unique id
2. I have already done database checking for identical username. but that is if there is a value in the database, what if they both does it at exactly the same time

Answer 6

thank you, I will look on it