MySQL- Prevent duplicate key constraint violations

Today, I found 3 different ways in MySQL to do an insert in a table without having to check if the key exists. This is mainly used for relationships where the application doesn't care if they previously existed or not. It should NOT be used for anything else, IMHO.

Source: http://lists.mysql.com/mysql/169721
INSERT INTO users_roles (user_id, role_id) VALUES (1,1) ON DUPLICATE KEY UPDATE role_id = role_id;

However, that doesn't look too clean and I'm still unsure about possible side-effects.

Source: http://forums.mysql.com/read.php?97,164551,164575#msg-164575
INSERT INTO users_roles (user_id,role_id) select 1,1 from dual where not exists (select user_id from users_roles where user_id=1 and role_id=1)

This is better, but still. It's difficult to use the select value1, value2 from dual in a prepared statement. And since I got more involved in projects where we had to fix SQL injection issues, there's no way I will be using anything like this.

In the same thread, however, I found this wee one.

INSERT IGNORE INTO users_roles (user_id,role_id) values(1,1)

Now this one is my favorite. It's beautiful in it's simplicity. But it should be used sparingly and with great caution as it could lead to unexpected bugs if misused. But in this case, a relationship that should be created in any case, it is a nice simple option.

Detect which foreign key gives issues

When dealing with generated foreign key names, it's not always obvious which are the related tables. When you get a foreign key issue, you can get more details in two steps.

Step 1: show innodb status
This will give us something like:

------------------------
LATEST FOREIGN KEY ERROR
------------------------
100308 15:22:29 Error in foreign key constraint creation for table `/`.
A foreign key constraint of name `/`
already exists. (Note that internally InnoDB adds 'databasename/'
in front of the user-defined constraint name).

Step 2: Execute the following query to determine the details of this constraint:

select
  constraint_name as 'name',
  concat(table_name, '.', column_name) as 'foreign key',     concat(referenced_table_name, '.', referenced_column_name) as 'references'
from
  information_schema.key_column_usage
where
  referenced_table_name is not null
  and constraint_name='<foreignkeyname>';