Is it possible to use IGNORE based on a field other than the PK, is it just a Single Index?

Question

Is it possible to use IGNORE based on a field other than the PK, is it just a Single Index?

Navigation

#1 by (2 votes)

2

Considering the following modeling:

ID INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY
PONTO INT (10) NOT NULL UNIQUE
ENDEREÇO INT (10) NOT NULL (FOREIGN KEY)

Where the ID field is an Auto Increment field, the PONTO field is a field with unique values (can not be repeated, as a PK) and ENDEREÇO is a relationship with another table.

Can I use IGNORE based on the PONTO field or should I make it PK ?

If it is possible (use ignore based on ponto ), using Last Index features of MySQLi and PDO (in PHP) would return ID if the item already existed in the bank?

php mysql

asked by anonymous 04.11.2015 / 21:20

1 answer

Blogger: How can I display only the posts of a specific label on the blog homepage? Login with Cookie or Session in PHP5 [duplicate]

score 2 · Accepted Answer

It's okay to use IGNORE in any single key, the effect is the same.

IGNORE acts on both PK violations and other constraints , such as UNIQUE. So much so that you can use INSERT IGNORE if your table has some constraint in this format, including:

alter table vote add constraint unique (ponto, endereço);

In case of a constraint violation, the insert simply does not happen, and only a warning is generated.

Just to be aware, remember also that INSERT … ON DUPLICATE KEY UPDATE exists which may be more interesting in some cases (maybe not in your example, which seems to me to be a kind of key-value relationship).

Note:

If you plan to use LAST_INSERT_ID() , you will need some care. According to documentation :

If you use INSERT IGNORE and the row is ignored, the LAST_INSERT_ID () remains unchanged from the current value (or 0 is returned if successful)

That translating freely is

If you use INSERT IGNORE and the line is ignored, the LAST_INSERT_ID () remains as it was previously (or 0 is returned if there was no INSERT successful)

This means that you should check if there was even a real insert, because if it does not exist, the LAST_INSERT_ID () value may be contaminated and this can be disastrous for the application.

It may be the case to combine the result with MYSQL_AFFECTED_ROWS ( ) to know if the returned value is actually usable. If the returned value is 0 , it is a sign that the returned ID does not match the query that has just been performed.