Check for Duplicates in a Database Before Entering Data

Question

Before Entering data into a database, I just want to check that the database doesn't have the same username in the database already.

I have the username in SQL set as a key, so it can't be duplicated on that end, but I am looking at finding a more user-friendly error message then "KEY already exists".

Is there are simple way to check if the variable value already exists in a row?

Thanks!

Answer 1

I can think of two ways to do this.

The first is simply to do a select statement beforehand on that username to detect any duplicates. If a row is returned then you know that that username already exists.

The other is that you can get mysql to return the error number using mysql_errno, you can then simply have an if statement that checks for a specific error number. The only problem with this that it may not indicate which field is a duplicate of the key.

I have used a pre select statement in my scripts.

Answer 2

The technique to check whether data exists is to issue the query:

 SELECT COUNT(*) FROM YourTable WHERE YourKeyCol = YourKeyValue

and then examine the first returned column of the only returned row in the dataset. If it contains 0, the data wasn't there, otherwise it was found.

But as others have pointed out, you can just go ahead and issue your INSERT. Examine the error code to determine whether it failed. This is more performant because, for those cases where the data is not already in the database, you will execute only one query instead of two.

Answer 3

There might be a special way to do this depending on what RDBMS you are using. For example, using MySQL, you can say

INSERT INTO table (username,value) VALUES ('foo',123) ON DUPLICATE KEY UPDATE value = 123;

Since you've already set username to be a unique key, this will insert ('foo',123) into table only if foo is not already in the table. If it does exist, then the value is updated.

Or, you could use something like

INSERT IGNORE INTO table (username,value) VALUES ('foo',123)

which ignores the insert if foo is already in the table.