Preventing my database from receiving the same data twice

Question

I have been working on a database using PHP. I discovered that the database will accept the same email if I use it to register a dozen times. How do I make the database reject any data that is already used in my database?

score 1 · Answer 1 · answered May 21 '16 at 20:14

You should probably validate your input form first. Before trying to insert the data into the database, check the validity - is the email address already present in my database? If it is, add a message stating that information (The email address you entered is already in use).

Additionally, you might want to use a unique index on the email address column. This prevents inserting the same database row a second time. You need to make sure to react on the resulting database error (cannot insert row), though. See http://www.w3schools.com/sql/sql_unique.asp for an example.

score 0 · Answer 2 · edited May 21 '16 at 20:51

0

Alter your table to make the email field UNIQUE. The database will then prevent the insertion of duplicate rows for you. Change it with this query:

ALTER TABLE <your_table_name> CHANGE COLUMN email email VARCHAR(255) UNIQUE NOT NULL`

That's assuming the column is named email. If it is not you will need to rename it.

edited May 21 '16 at 20:51

jeteon

3,471
27
40

answered May 21 '16 at 20:12

dimlucas

5,040
7
37
54

1

And as a bonus, if you make it unique the database will put an index on the column for you so queries against the column will be faster too. – jeteon May 21 '16 at 20:51
Thanks guys. It worked – mcperfect May 21 '16 at 21:35

Preventing my database from receiving the same data twice

2 Answers2