1

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.

  • Is the question only about FOREIGN KEY constraints only or all kinds of constraints? – ypercubeᵀᴹ Jan 12 '16 at 8:45
  • @YperSillyCubeᵀᴹ: I narrowed the question to foreign keys only. – Krzysztof Antoniak Jan 12 '16 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 Jan 12 '16 at 10:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.