In a MySQL database table, I have a new user registry, the primary key of the table being an ID field. However, I may end up creating, even by accident, users with the same username.
For example, "joao.silva" can be registered for users named "João Carlos da Silva" and "João Silva Neto". However, the login will only work for one of them.
Is there any configuration that can be done in MySQL so that this table does not allow identical usernames?
Make this column a unique key that will return an error when an existing value is entered or updated:
ALTER TABLE tabela ADD UNIQUE INDEX 'nome_indice' ('coluna');
The safest way would be to add a unique constraint, so in no way will you have the same usernames.
In your application, you handle the "1062" code error that is related to this single key violation and returns a message stating.
The code to add this key after the created table is as follows:
ALTER TABLE tb_usuarios
ADD CONSTRAINT uc_usuario_unico UNIQUE (campo_user)
Where tb_users is your table, uc_user is an identifier for this unique key and field_user is the field that should be unique.