What kind of errors exists in SQL querys for ROLLBACK?

For example:

insert into table( a, b ) values ('a','b') could generate the following error:

**a-b duplicate entry**

BUT here I can 开发者_运维知识库ignore this error selecting the ID of this values, then use this ID:

select ID from table where a = 'a' and b = 'b'
insert into brother( table ) values (ID)

Finally I could COMMIT the PROCEDURE. Look that this error isn't relevant for rollback if I need the ID.

The question is: what kind of errors will doing me to ROLLBACK the PROCEDURE???

I hope you understand.

I think you're asking, "What kind of errors can an INSERT statement cause that will make MySQL rollback a transaction?"

An INSERT that violates any constraint will cause a rollback. It could be foreign key constraint like you've outlined, but it could also be a UNIQUE constraint, or a CHECK constraint. (A CHECK constraint would probably be implemented as a trigger in MySQL.)

Trying to insert values that aren't valid (NULL in nonnullable columns, numbers that are out of range, invalid dates) might cause a rollback. But they might not, depending on the server configuration. (See link below.)

An INSERT can also fail due because it lacks permissions. That will also cause a rollback.

Some conditions that would cause a rollback on other platforms don't cause a rollback on MySQL.

The options MySQL has when an error occurs are to stop the statement in the middle or to recover as well as possible from the problem and continue. By default, the server follows the latter course. This means, for example, that the server may coerce illegal values to the closest legal values.

That quote is from How MySQL Deals with Constraints.

One of my favorite quotes from the MySQL documentation, 1.8.6.2. Constraints on Invalid Data.

MySQL enables you to store certain incorrect date values into DATE and DATETIME columns (such as '2000-02-31' or '2000-02-00'). The idea is that it is not the job of the SQL server to validate dates.

Isn't that cute?