Avoid double inserted information in database

Question

Hey guys I have a table called username, and in my programm i have the possibility to create new users with an username and a password.

But I dont want to have two users with the same username

Im using php and mysql.

How can I handle this?

thx nubu

[Differences between INDEX, PRIMARY, UNIQUE, FULLTEXT in MySQL?](http://stackoverflow.com/questions/707874/differences-between-index-primary-unique-fulltext-in-mysql) — Ja͢ck, Mar 11 '13 at 14:42

John Woo · Answer 1 · 2013-03-11T15:00:48.173

0

add UNIQUE constraint so to enforce distinct values on the column,

ALTER TABLE tableNAme ADD CONSTRAINT tb_unique UNIQUE(username)

edited Mar 11 '13 at 15:00

answered Mar 11 '13 at 14:38

John Woo

I altered my table and added an unique index ... after that i tried to add a new user with the same username. it didnt work ... o_o :( – Nubu Mar 11 '13 at 14:42
can you post the result of this statement? `DESC yourTableName`? – John Woo Mar 11 '13 at 14:44
sorry i think i wrote it a little bit weird, it works, i can add users into the db BUT also users with the same usernamen eventhough i added this unique index u talke about – Nubu Mar 11 '13 at 14:50
see my updated answer. – John Woo Mar 11 '13 at 15:01
what is that tb_unique? – Nubu Mar 11 '13 at 15:18
it is the name of the `constraint`, you can name whatever you want `:)` – John Woo Mar 11 '13 at 15:21
error ... duplicate entry for my Constraint name (unique_username) ... – Nubu Mar 11 '13 at 15:23
because there are already records in your table. try to remove the duplicates of empty your table first then execute the query. – John Woo Mar 11 '13 at 15:25
im pretty sure that i added this unique property but its still not working ... i saw ur example its pretty easy to understand ... but when it comes to my case it doenst work ... ? o.O EDIT: Ah k i will try it – Nubu Mar 11 '13 at 15:25
As I've said, there are already records on your table. If you try to empty it first or remove those duplicates, it will surely work. – John Woo Mar 11 '13 at 15:26
Ok it works :-D Thx alot! – Nubu Mar 11 '13 at 15:38
you're welcome `:D` glad to help you. – John Woo Mar 11 '13 at 15:41

score 0 · Answer 2 · answered Mar 11 '13 at 14:40

0

You can check if the name already exists an ask the user to select a different name;

SELECT COUNT(username) FROM users WHERE userName = '[name to check]'

and add a unique index to the username field

answered Mar 11 '13 at 14:40

splash21

can i realize it in php and if yes how ... that i check every column and if the username is equals to my post ... its not possible to save this user? – Nubu Mar 11 '13 at 14:44

2 Answers2