Detect MySQL Update/Insertion Failure Due to Violated Unique Constraint

Detect mysql update/insertion failure due to violated unique constraint

http://dev.mysql.com/doc/refman/5.5/en/error-messages-server.html

http://php.net/manual/en/function.mysql-errno.php

I've had to do this in the past, and it's not fun:

if( mysql_errno() == 1062) {
// Duplicate key
} else {
// ZOMGFAILURE
}

A note on programming style (Credits to jensgram from this answer)

You should always seek to avoid the use of magic numbers. Instead, you could assign the known error code (1062) to a constant (e.g. MYSQL_CODE_DUPLICATE_KEY). This will make your code easier to maintain as the condition in the if statement is still readable in a few months when the meaning of 1062 has faded from memory :)

PHP MySQL INSERT fails due to unique constraint

If you are attempting to insert a row if new or update existing values then REPLACE INTO is what you need. Also consider INSERT ... ON DUPLICATE KEY UPDATE Syntax if there are constraints involved as REPLACE INTO will DELETE and then INSERT.

INSERT and detect unique constraint error

It is not possible to use that statement as it is. A violation of a unique key will raise an error. You will have to catch that error, either with insert ignore (which does not allow you to react to your 3rd condition), or with on duplicate key.

You can use an on duplicate key that will intentionally throw an error if your conditions are met. Unfortunately, the errormessage will not be directly related to the violation, it will just be "any" exception (an exception that describes your situation does not exist anyway). In the update-part, check if the name is different. If it is unchanged, you do nothing ("ignore"), if it is changed, you can set an invalid value to e.g. throw a not null-error:

INSERT INTO devices_used_by_resource(id,sourcesId,name) 
VALUES(123,321,'the name')
on duplicate key update id = if(name = values(name), id, null);

> 1 row(s) affected

INSERT INTO devices_used_by_resource(id,sourcesId,name)
VALUES(123,321,'the name')
on duplicate key update id = if(name = values(name), id, null);

> 0 row(s) affected

INSERT INTO devices_used_by_resource(id,sourcesId,name)
VALUES(123,321,'the name1')
on duplicate key update id = if(name = values(name), id, null);

> Error Code: 1048. Column 'id' cannot be null

You have a second unique index on (name, sourcedId), that will also trigger on duplicate key. You didn't specifiy what should happen if you insert a row that violates this, so the statement will just ignore it (a violation of (name, sourcedId) will not change the name, so no exception is thrown):

INSERT INTO devices_used_by_resource(id,sourcesId,name) 
VALUES(1,321,'the name')
on duplicate key update id = if(name = values(name), id, null);

> 0 row(s) affected

If you want to throw an exception then too, you can compare all values instead of just the name, so a different id will also raise an error:

INSERT INTO devices_used_by_resource(id,sourcesId,name) 
VALUES(123,321,'the name1')
on duplicate key update
id = if(id = values(id) and sourcesId = values(sourcesId)
and name = values(name), id, null);

> Error Code: 1048. Column 'id' cannot be null

The exception message is obviously not very clear about the error, because it raises an unrelated error. If you just want to catch that exception and know what it actually means, that will be fine. To make it a little more stylish, you could add a trigger that uses id=null as an indicator to throw a custom message, e.g.

delimiter $$
create trigger trbu_devices_used_by_resource
before update on devices_used_by_resource
for each row
begin
if new.id is null then
SIGNAL SQLSTATE '45000'
SET message_text = 'Inserted duplicate entry with different attributes!';
end if;
end $$
delimiter ;

It will also throw that error if you use update devices_used_by_resource set id = null without an insert, but I guess that does not happen that often, or maybe you can find a message that covers that too - or you do some more complicated communication between the upsert and the trigger, e.g. set the id or sourcesId to -812677 and check for that values in the trigger.

When Would This INSERT.. ON DUPLICATE Fail?

This will happen if fixing the first uniqueness constraint (by updating the conflicting row instead of inserting a new one) causes the second one to fail.

For example, let's say your table already has the following rows:

(6, 494, NULL, 'category/123', 'lessons/teacher-s-planning-calendar/n-a', 'catalog/category/view/id/123', 1),
(6, 494, NULL, 'category/494', 'lessons/foobar/whatever', 'catalog/category/view/id/494', 1);

Then trying to insert your new row:

(6, 494, NULL, 'category/494', 'lessons/teacher-s-planning-calendar/n-a', 'catalog/category/view/id/494', 1)

will cause the first constraint to fail (since a row with request_path = 'lessons/teacher-s-planning-calendar/n-a' and store_id = 6 already exists in the table), and thus the ON DUPLICATE KEY UPDATE clause will cause the conflicting row to be updated instead. But this will cause it to violate the second constraint, since there's already another row in the table with id_path = 'category/494', is_system = 1 and store_id = 6.

Here's a simple example on SQLize demonstrating this behavior.


In fact, what really happens is that, if an INSERT statement with ON DUPLICATE KEY UPDATE violates multiple uniqueness constraints, MySQL will try to apply the update to all the conflicting rows. For an update like this, setting all the columns to fixed values, that's pretty much guaranteed to result in a further constraint violation, since it's effectively trying to make two different rows in table identical.

As the linked documentation notes:

In general, you should try to avoid using an ON DUPLICATE KEY UPDATE clause on tables with multiple unique indexes.

Check constraint is violated even though the data is corresponding with it

You are using SQL Server syntax not applicable to MySql.

In MySql [] are not wildcard characters when used with the operator LIKE.

You can use REGEXP:

CREATE TABLE customer
(
customerid CHAR(5) NOT NULL,
PRIMARY KEY (customerid),
CONSTRAINT cekcustomerid CHECK (customerid REGEXP '^CU[0-9]{3}$')
);

See the demo.

How to find the record that violate unique key constraint?

If you don't have a unique key on col_b, col_c, col_d of table_b, this will result in a violation when copying over. You can identify problematic rows with a query like this:

SELECT col_b, col_c, col_d
FROM table_b
GROUP BY col_b, col_c, col_d
HAVING COUNT(*) > 1

A similar query can be run on table_a joined to table_b, but the specific queries to run will depend on which columns will be updated in table_a. For the insert case, a useful technique might be to use a MINUS between table_a and the proposed inserted row.

Unique key with NULLs

A fundamental property of a unique key is that
it must be unique. Making part of that key Nullable destroys this property.

There are two possible solutions to your problem:

  • One way, the wrong way, would be to use some magic date to represent unknown. This just gets you past
    the DBMS "problem" but does not solve the problem in a logical sense.
    Expect problems with two "John Smith" entries having unknown dates
    of birth. Are these guys one and the same or are they unique individuals?
    If you know they are different then you are back to the same old problem -
    your Unique Key just isn't unique. Don't even think about assigning a whole range of magic dates
    to represent "unknown" - this is truly the road to hell.

  • A better way is to create an EmployeeId attribute as a surrogate key. This is just an
    arbitrary identifier that you assign to individuals that you know are unique. This
    identifier is often just an integer value.
    Then create an Employee table to relate the EmployeeId (unique, non-nullable
    key) to what you believe are the dependant attributers, in this case
    Name and Date of Birth (any of which may be nullable). Use the EmployeeId surrogate key everywhere that you
    previously used the Name/Date-of-Birth. This adds a new table to your system but
    solves the problem of unknown values in a robust manner.

Acceptable way to get around a UNIQUE w NULL constraint?

Relational Solution

Responding to the relational-database tag, in that context. SQL is the data sublanguage defined by Codd in his Relational Model, that was established as a Standard in the 1980's. Thus SQL is Relational; Relational is SQL.

  • All items in this answer are Relational and can be implemented easily in SQL, we have had them for decades.


Is this a poor approach to use or does this seem like a good way to enforce the Unique-ness constraint here? If it's not a good way, what might be a better way?

Yes, it is poor, on two counts. First, you are trying to do too many things in one file. Second, it is not Relational, and there is a clean; logical way to do that within the Relational paradigm: Therefore your intended addition is problematic.

  • this is not a database design question, but strictly answering what is wrong, with an explanation.
  1. Container

    container_id; name and parent_container_id indicate a simple, single-parent hierarchy. That is one Fact.

    • since you have more than one data tree or hierarchy, each of which has a Root, you need an Anchor row with container_id 0, that the roots are children of, and to inform the recursive Function that constructs the Path or PathName when to stop. This is an Anchor row, it means something, it is not a fiddle to avoid Null.
    • the AK ( parent_container_id, name ) is required to prevent duplicate names within a parent_container_id. That is one Predicate. Do not double up and try to do more with it, add another Predicate instead.
  2. Connection

    connection_id is a separate discrete Fact. I presume it applies only to a Root. So this is a Subordinate Fact, which is optional to [1]. Its integrity is maintained by a constraint that calls a Function (same as in the Subtype doc) that checks the parent is a Root.

    • Null is a red flag indicating incomplete Normalisation, if you Normalise the data, you will not store a Null. (Nulls in result sets are allowed, of course.)

    • Separately, as a rule, since Null is the unknown, the notion of a Key (UNIQUE) that is unknown, is hysterical. That is why the Standard prohibits it.

  3. "Union Type"

    In the Relational paradigm, the concept of "union type" is completely bankrupt. It is a method used by academics, who do not understand the Relational Model or SQL, who teach and promote 1960's Record Filing Systems and database systems that do not implement the SQL standard correctly. Since those database systems do not support SQL (Constraint that calls a Function), the method is an alternative that is possible: an additional index in the parent and an additional Foreign Key in the child. Horrendous at every level. Do not use it as a generic method.

    • note that the additional index is perverse in the index sense (container_id is already unique; adding something to an unique field does not make it more unique), its only purpose is to allow a Foreign Key in the child, that in turn is checked for some condition (here, that the parent is a Root).

    • obviously, if your "SQL" isn't SQL, use the pretend "SQL" method

  4. Disjunction

    To implement an OR or XOR Gate (formal disjunction), use a proper Subtype cluster (instead of overloading the already poor file that you have).

Data Model

foo

This model provides a relational-database solution, which implies SQL, and therefore proper Constraints that can call a Function.

  • It is impaired because the "Primary Key" is not a Key but a physical RecordId, that does not have the properties of a logical Key. The consequence will be noticed in child tables.

foo

This model provides a solution that is possible in MySQL.

  • the typical 1960's pre-Relational, pre-DBMS, physical Record Filing System, that is marketed by the academics as "relational",

  • with the additional index and FKs to substitute for SQL Constraints, for use in the databases that do not implement SQL correctly

Notation

  • All my data models are rendered in IDEF1X, available from the early 1980's, the one and only Standard for Relational Data Modelling 1993.

  • The IDEF1X Introduction is essential reading for those who are new to Codd's Relational Model, or its modelling method. Note that IDEF1X models are complete, they are rich in detail and precision, showing all required details, whereas a home-grown model, being unaware of the imperatives of the Standard, have far less definition. Which means, the notation needs to be fully understood.



Related Topics



Leave a reply



Submit