Hacker News Reader

Comment by AlphaSite | Hacker News Reader

Doesn’t innodb atleast enforce those constraints?

During most normal operations such as updates, inserts and deletes it will enforce them. But, opposed to other databases, you are allowed to completely ignore your foreign key constraints leaving you with a (in my opinion) possibly corrupt database.

  SET FOREIGN_KEY_CHECKS = 0;
  INSERT INTO ...
  SET FOREIGN_KEY_CHECKS = 1;

Now you might have data in your database that's actually incorrect.

You can do this in postgres as well, with something like:

    begin; alter table t2 disable trigger all;
    insert into t2 (t1_id) values (20);
    alter table t2 enable trigger all;
    commit;

Setting constraints to deferrable/initially deferred[1] is probably a nicer way for any non-insane thing you're trying to achieve with such, though :)

[1] https://www.postgresql.org/docs/9.6/static/sql-set-constrain...

PostgreSQL has the same feature actually, which I learned as part of a MySQL -> PostgreSQL migration that's underway.

    SET session_replication_role TO 'replica';

We need this to load the data in without worrying about table order, or even individual row order for foreign keys that reference their own table.

And we'll all try and forget this feature exists when we're done!

Reply on news.ycombinator.com