Is it possible to use IGNORE based on a field that is not PK, it is only a Single Index?

Question

Taking into account 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 field ID is a Auto Increment, the field PONTO is a field with unique values (cannot be repeated, like a PK) and ENDEREÇO is a relationship with another table.}

Is it possible for me to use the IGNORE based on the field PONTO or should I make it to PK?

If possible (use ignore on the basis of ponto), use the resources of Last Index Mysqli and PDO (in PHP) would return the ID if the item already existed in the bank?

Answer 1

No problem to use IGNOREin any single key, the effect is the same.

the IGNORE acts on both PK violations and other constraints, like UNIQUE. So much so that you can use INSERT IGNORE if your table has any Constraint in this format, including:

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

In the event of infringement of Constraint, simply Insert does not happen, and only one is generated Warning (that will be ignored without problems by the application).

For the record, also remember that there is a INSERT … ON DUPLICATE KEY UPDATE 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 intend to use the LAST_INSERT_ID(), will need some care. According to the 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 the Connection has not yet performed a Successful INSERT)

Which translating freely is

If you use INSERT IGNORE and the line is ignored, LAST_INSERT_ID() remains as before, (or 0 is returned if there has been no successful INSERT)

This means that you should check if there was even a real insertion, because if there is not, the value of LAST_INSERT_ID() can be contaminated and this can be disastrous for the application.

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