By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,985 Members | 1,863 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,985 IT Pros & Developers. It's quick & easy.

Error checking for unique value in database table?

P: n/a
I am inserting data into user table which contains 5 fields, sounds simple
enough normally but 2 of the fields are designated as UNIQUE. If someone
does enter a value which already exists, how do I capture this specific
error?

Would it make more sense to actually run a SELECT query first and if that
returned a result, then I use that for error checking and don't run insert
until select returns nothing?

Cheers

Phil

May 23 '07 #1
Share this Question
Share on Google+
10 Replies


P: n/a
Phil Latio wrote:
I am inserting data into user table which contains 5 fields, sounds simple
enough normally but 2 of the fields are designated as UNIQUE. If someone
does enter a value which already exists, how do I capture this specific
error?
Hi,

You capture the error by using the errormessage on your connection.
How the function is named depends on the database you are using, or the
database abstraction layer.
>
Would it make more sense to actually run a SELECT query first and if that
returned a result, then I use that for error checking and don't run insert
until select returns nothing?
Yes.
I would say that is the right way of doing it with style.

You could of course try to parse your errormessage and look for substrings
like 'UNIQUE CONSTRAINT VIOLATION' (just an example), but that message
could very well be different if you switch databases, or upgrade the one
you use now.

So I would go with the general solution you described yourself: check first
for violation of UNIQUE constraint, then insert.

Regards,
Erwin Moller
>
Cheers

Phil
May 23 '07 #2

P: n/a
Phil Latio wrote:
I am inserting data into user table which contains 5 fields, sounds simple
enough normally but 2 of the fields are designated as UNIQUE. If someone
does enter a value which already exists, how do I capture this specific
error?

Would it make more sense to actually run a SELECT query first and if that
returned a result, then I use that for error checking and don't run insert
until select returns nothing?

Cheers

Phil


Phil,

Look at the error code returned by the database.

Most databases return an error code and an error message. The error
message is great for humans; the error code better for computers, and
should be unique (IIRC, this one is 1062 for MySQL).

Don't do a select first. It's unnecessary overhead and not guaranteed
unless you lock the tables first (more overhead). For instance, you
might search for "john" and not find it - but before you can add "john",
another process adds it. Now when you try, you get a duplicate, even
though you checked first.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================
May 23 '07 #3

P: n/a
Erwin Moller wrote:
You could of course try to parse your errormessage and look for substrings
like 'UNIQUE CONSTRAINT VIOLATION' (just an example), but that message
could very well be different if you switch databases, or upgrade the one
you use now.
A good compromise is to parse the error message , but in cases where it
can't be recognised, display the error message directly to the user.

--
Toby A Inkster BSc (Hons) ARCS
[Geek of HTML/SQL/Perl/PHP/Python/Apache/Linux]
[OS: Linux 2.6.12-12mdksmp, up 88 days, 18:28.]

The Great Wi-Fi Controversy
http://tobyinkster.co.uk/blog/2007/05/22/wifi-scare/
May 23 '07 #4

P: n/a
Jerry Stuckle wrote:
Phil Latio wrote:
>I am inserting data into user table which contains 5 fields, sounds
simple enough normally but 2 of the fields are designated as UNIQUE. If
someone does enter a value which already exists, how do I capture this
specific error?

Would it make more sense to actually run a SELECT query first and if that
returned a result, then I use that for error checking and don't run
insert until select returns nothing?

Cheers

Phil



Phil,

Look at the error code returned by the database.

Most databases return an error code and an error message. The error
message is great for humans; the error code better for computers, and
should be unique (IIRC, this one is 1062 for MySQL).

Don't do a select first. It's unnecessary overhead and not guaranteed
unless you lock the tables first (more overhead). For instance, you
might search for "john" and not find it - but before you can add "john",
another process adds it. Now when you try, you get a duplicate, even
though you checked first.
Is that good advise Jerry?
If you use the errormessage, or better errorcode as you suggest, the PHPcode
is specialized for that specific database.
Will, as in your example, mySQL use the same code if you use a different
version? (I hope it does, but I am unsure.)
Not to mention a switch of database.

Probably just a matter of taste, but I always felt that doing the 1 select
to check for the value isn't that much overhead, and you, the programmer,
can handle a conflict of unique constaint gracefully, even if the errorcode
changes or the database changes.
No need to change PHP code, in theory. ;-)

I always regarded the fact that somebody else inserted 'john' between my
select and insert farfetched. If it happens: well, they get an error, but
chances of that happening are small (at least in all situations I have
seen).

Another reason I use SELECT to check for UNIQUE constraints before
inserting:
If you have for example 2 UNIQUE columns (eg nickname, loginname), you'll
get a errorcode, but you cannot tell the user which one was causing it.

just my 2 cent.

Regards,
Erwin Moller
May 23 '07 #5

P: n/a
If you have for example 2 UNIQUE columns (eg nickname, loginname), you'll
get a errorcode, but you cannot tell the user which one was causing it.
This is exactly the reason why I raised the question.

Cheers

Phil
May 23 '07 #6

P: n/a
Erwin Moller wrote:
I always regarded the fact that somebody else inserted 'john' between my
select and insert farfetched. If it happens: well, they get an error, but
chances of that happening are small (at least in all situations I have
seen).
It is for this sort of reason that transactions exist.

--
Toby A Inkster BSc (Hons) ARCS
[Geek of HTML/SQL/Perl/PHP/Python/Apache/Linux]
[OS: Linux 2.6.12-12mdksmp, up 89 days, 5 min.]

The Great Wi-Fi Controversy
http://tobyinkster.co.uk/blog/2007/05/22/wifi-scare/
May 23 '07 #7

P: n/a
Erwin Moller wrote:
Jerry Stuckle wrote:
>Phil Latio wrote:
>>I am inserting data into user table which contains 5 fields, sounds
simple enough normally but 2 of the fields are designated as UNIQUE. If
someone does enter a value which already exists, how do I capture this
specific error?

Would it make more sense to actually run a SELECT query first and if that
returned a result, then I use that for error checking and don't run
insert until select returns nothing?

Cheers

Phil


Phil,

Look at the error code returned by the database.

Most databases return an error code and an error message. The error
message is great for humans; the error code better for computers, and
should be unique (IIRC, this one is 1062 for MySQL).

Don't do a select first. It's unnecessary overhead and not guaranteed
unless you lock the tables first (more overhead). For instance, you
might search for "john" and not find it - but before you can add "john",
another process adds it. Now when you try, you get a duplicate, even
though you checked first.

Is that good advise Jerry?
If you use the errormessage, or better errorcode as you suggest, the PHPcode
is specialized for that specific database.
Will, as in your example, mySQL use the same code if you use a different
version? (I hope it does, but I am unsure.)
Not to mention a switch of database.
Your code is typically database specific in many ways, anyway. Even the
SQL statements can vary between databases.

But yes, error codes are consistent across all versions of MySQL - and
most of them are consistent across databases also. There are standards
for the majority of the error codes.
Probably just a matter of taste, but I always felt that doing the 1 select
to check for the value isn't that much overhead, and you, the programmer,
can handle a conflict of unique constaint gracefully, even if the errorcode
changes or the database changes.
No need to change PHP code, in theory. ;-)
It is extra overhead, and it does leave an opening for someone else to
sneak in. It's just this type of programming which causes very
difficult to find errors - ones that occur only once every few weeks,
for instance.
I always regarded the fact that somebody else inserted 'john' between my
select and insert farfetched. If it happens: well, they get an error, but
chances of that happening are small (at least in all situations I have
seen).
It's not at all far fetched. In fact, I can tell you about a time on a
mainframe where a 4 machine-code instruction sequence would occasionally
fail because it got interrupted in the middle of those 4 statements -
and a flag which was checked by on entry to the sequence got changed.

Remember - you're working with a multitasking system. NEVER assume your
code will not be interrupted!
Another reason I use SELECT to check for UNIQUE constraints before
inserting:
If you have for example 2 UNIQUE columns (eg nickname, loginname), you'll
get a errorcode, but you cannot tell the user which one was causing it.
Does it really matter? But if it does, then you can go back and
determine which one (or both) is causing the problem.

But proper database normalization also says you won't have a row
dependent on two unique constraints (but it might be dependent on one
constraint with multiple columns).
just my 2 cent.

Regards,
Erwin Moller

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================
May 24 '07 #8

P: n/a
Jerry Stuckle wrote:
Your code is typically database specific in many ways, anyway. Even the
SQL statements can vary between databases.
Error messages will vary between locales, even using the same database and
version on the same OS!

--
Toby A Inkster BSc (Hons) ARCS
[Geek of HTML/SQL/Perl/PHP/Python/Apache/Linux]
[OS: Linux 2.6.12-12mdksmp, up 89 days, 13:19.]

The Great Wi-Fi Controversy
http://tobyinkster.co.uk/blog/2007/05/22/wifi-scare/
May 24 '07 #9

P: n/a
Toby A Inkster wrote:
Erwin Moller wrote:
>I always regarded the fact that somebody else inserted 'john' between my
select and insert farfetched. If it happens: well, they get an error, but
chances of that happening are small (at least in all situations I have
seen).

It is for this sort of reason that transactions exist.
Very true.
And I use them for this kind of things. :-)
I should have written that.

Thx.

Regards,
Erwin Moller
May 24 '07 #10

P: n/a
It is for this sort of reason that transactions exist.

Thanks for the suggestion of using transactions. Hadn't really occured to me
and had never employed them before.

I have found a chapter in PHP book I use that covers the use of transactions
so I should be able to follow that.

Cheers

Phil
May 25 '07 #11

This discussion thread is closed

Replies have been disabled for this discussion.