Check if foreign keys is integrity before insert

Question

Check if foreign keys is integrity before insert

I have two tables that follow and profile, and follow in the table insert id of the profile below (foreign keys)

How can I be sure before inserting into my table foreign keys that match a profile id of the table?

var o_segui = {
    seguace_id: profilo,
    seguito_id: data.profilo_da_seguire,
    created: new Date()
};

connection.query('INSERT INTO follow SET ?', o_segui, function(error, rows) {
 if (error) {
     var err = "Error on 'seguiAction': " + error;
     console.error(err);
     throw err;
 }

now i check that now exists with a query before insert like:

connection.query('SELECT count(*) as n FROM profile WHERE id =' + connection.escape(profilo_da_seguire), function(error, rows) {
if (error) {
    var err = "Error on 'verifico Profilo': " + error;
    console.error(err);
    throw err;
}

console.log(rows);

if (rows.shift().n > 0) { then OK

UPDATE in my Insert i do throw err...if i comment throw err server node dosen't go down...i have to comment this line and close connection ?

mysql
node.js

Answer 1

Check-before-set is usually not a good approach while using database. Mostly because you will have to take into account possible concurrent access to your tables.

Anyway, concerning foreign key constraints, if you use InnoDB, the engine will check for you at insert-time.

So, I think it is better to be optimistic by trying to insert. But be prepared to handle gracefully a possible failure.

EDIT: I build an example in order to "prove" that integrity constraints are enforced by the DB engine InnoDB: http://sqlfiddle.com/#!2/f372d/1

As you will see, faulty data are not inserted in the DB. In your program you will have to deal with the corresponding exception/error.