Can there be any proper error handling be implemented into a SQL UNIQUE Constraint? So far i'm obviously getting: Error: Duplicate entry 'test1' for key 'username'
But i'd like to have it display a custom comment if possible
-
I don't know PHP but usually you get an error code back from your database driver. For Oracle the error number would be 1, for Postgres 23505, ... Catch the exception, check the error number and act accordingly in the code.user330315– user3303152013-12-04 16:13:30 +00:00Commented Dec 4, 2013 at 16:13
Add a comment
|
1 Answer
You should check in your app code before inserting this
row that no other row exists with the same username.
Then if it exists, show the error you want to the user.
Parsing this unique constraint error
(after you get it) is not a good idea.
So do it before, not after.
4 Comments
Checking the rows first will simply double the work the database is doing. First your application checks it, then upon insert the database checks again. Handling the exception is much more efficient approach. And you don't need to "parse" the error message. You can check the SQLSTATE or error code
peter.petrov
Yes, it's double work but I cannot agree with this. Maybe you're looking at it from the DB side only.
But that approach won't solve the problem. If you have multiple transactions then some other session can insert a row between in the time between the check (
select) and the actual insert. So you have to check for any error during insert anyway and in the end you wind up with the error checking and the double load on the server - which makes the application non-scalable.peter.petrov
These calls are usually synchronized through other means at the app or some middleware user management service level. You're talking about a typical 2-tier app in mind, I think. If so yes, I agree completely in that context. So anyway, I get your points too.