2

How do I disable FOREIGN KEY checking for the time of database schema migration?

Currently I found engine-based commands like:

  • PostgreSQL: SET CONSTRAINTS ALL DEFERRED;
  • MSSQL: EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all";
  • MySQL: SET FOREIGN_KEY_CHECKS = 0;
  • HSQLDB: SET REFERENTIAL_INTEGRITY FALSE;
  • DB2: SET INTEGRITY FOR table OFF;
  • SQLite 3.7.16: PRAGMA foreign_keys = false;
  • Oracle 11g seems to have no such command at all

Syntax significantly differ, in case of MSSQL I'm unable to recover previously disabled constraints (if any).

I look for possibly database-independent solution, or at least commands working for whole database of single engine. We can assume there is no constraint violation at the end of migration. Migration will be conducted in a transation.

Improve this question
3
  • Is the question only about FOREIGN KEY constraints only or all kinds of constraints?
    – ypercubeᵀᴹ
    Jan 12, 2016 at 8:45
  • @YperSillyCubeᵀᴹ: I narrowed the question to foreign keys only.
    – Krzysztof Antoniak
    Jan 12, 2016 at 8:56
  • I might be wrong, but I don't think the standard has anything to say about disabling/enabling foreign key constraints (which might explain the difference between vendors). You might want to check out each vendors procedural language, and create a procedure for each engine which you can call in the migration process.
    – Lennart - Slava Ukraini
    Jan 12, 2016 at 10:49

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.