Subverting foreign key constraints in postgres… or mysql

Temporarily disable key constraints?

On postgres (version 8.1, mind you) I ran across a scenario where I was had to update a set of records that belonged to the foreign key constraint hub in a schema I was working on. I basically had to update the table with a new set of that drive environmental variables. A run of the mill postgres replace function would not work as there was no natural regex replace that I could run that woud affect all the entries the way I wanted. I was looking for a single set of queries I could run in a transaction that I could run once and be done. It turns out that in postgres when you define a foreign key, you can flag it as DEFERRED:

ALTER TABLE tb_other ADD CONSTRAINT tb_other_to_table_fkey 
	FOREIGN KEY (tb_table_pk) REFERENCES tb_table (tb_table_pk) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE;

With the alter table command above we can then make use of this DEFERRABLE clause -this flag tells postgres that this constraint check may be deferred until the end of the transaction. The INITIALLY IMMEDIATE clause tells postgres the default constraint behavior is to check the constraint immediately, when the transaction attempts to perform the corresponding delete or insert. You can also flag the constraint to be INITIALLY DEFERRED. Initially deferring as you might guess tells postgres to check the constraint at the end of the transaction. I think generally if you want constraints though, you will probably want to check immediately. It’s good to know you have the option if you really need it though.

Once the foreign key constraint is set as deferrable, we can then execute a script like this to defer the constraint checks until the end of the transaction:

-- postgres deferred constraints in action
begin;

SET CONSTRAINTS ALL DEFERRED;

delete from tb_table;

insert into tb_table values ( nextval(sq_table), value1, value2, value3);
insert into tb_table values ( nextval(sq_table), value1, value2, value3);
insert into tb_table values ( nextval(sq_table), value1, value2, value3);

commit;

Pretty useful in my opinion. I think I prefer this solution as opposed to disabling triggers across the table since disabling triggers is a schema change and you end up being responsible for restoring them once you’re done. Consider the following :

-- postgres disabled triggers
begin;

ALTER TABLE tb_site DISABLE TRIGGER ALL;

delete from tb_table;

insert into tb_table values ( nextval(sq_table), value1, value2, value3);
insert into tb_table values ( nextval(sq_table), value1, value2, value3);
insert into tb_table values ( nextval(sq_table), value1, value2, value3);

-- make sure to restore the triggers
ALTER TABLE tb_site ENABLE TRIGGER ALL;

commit;

In this implementation you end up altering the schema to disable all the triggers associated with this table. Don’t forget to re-enable the triggers at the end of the transaction of the disabling will remain in place. Another thing to consider is if you have auditing type of triggers on your target table, you will then end up having to manually fire those triggers or run the appropriate clauses to perserve the original trigger’s integrity. This kind of thing could quickly turn into quite the problem if not handled correctly.

Mysql’d keys

The mysql approach handles this case very similar to the disabled triggers – instead, it uses a system variable called FOREIGN_KEY_CHECKS that can be toggled on or off:

-- mysql key constraint supression
begin;

-- lift 
SET FOREIGN_KEY_CHECKS=0;

delete from tb_table;

insert into tb_table values ( nextval(sq_table), value1, value2, value3);
insert into tb_table values ( nextval(sq_table), value1, value2, value3);
insert into tb_table values ( nextval(sq_table), value1, value2, value3);

-- put back when you're done
SET FOREIGN_KEY_CHECKS=1;

commit;

As you can see it’s a very similar approach to the trigger disable in postgres. From the documentation at the time of this writing (mysql version 5.5 – Deferred Foreign Keys in MySql) it looks like deferred keys are just not an option in mysql even though it’s listed as a standard. Worthy of notice.

References:
Postgres Set Constraints
Postgres Create Table documentation