Rails ActiveRecord migration to add foreign key with 'NOT VALID' parameter

Question

Can add_foreign_key add a 'NOT VALID' parameter to the ALTER TABLE command? (Postgres, if it mattters)

I have a foreign key between two very large tables. I need to add CASCADE DELETE to the key. It seems Postgres does not support adding the cascade to an existing foreign key. The solution is to drop the key, and add a new one with the cascade.

Easy enough...except that checking the constraint takes a LONG time. I'm not sure about any locking going on during that check, but really, I just want to skip it. The data is valid before I drop the constraint...it will be valid a moment later.

Postgres supports this with ALTER TABLE ADD CONSTRAINT ... NOT VALID. (Which means "skip validation", even though it sounds like you're saying that it is not valid. :shrug: )

So, can I get add_foreign_key to not validate?

Answer 1

From the manual: ActiveRecord::ConnectionAdapters::SchemaStatements#add_foreign_key

:validate

(Postgres only) Specify whether or not the constraint should be validated. Defaults to true.

So, to add a cascade delete to an existing foreign key constraint with no downtime:

remove_foreign_key :address, column: :user_id
add_foreign_key :address, :user_id, :users, on_delete: :cascade, validate: false

I initially searched for this answer with add_foreign_key "NOT VALID", for which I got no useful hits in the docs, StackOverflow, etc. Hopefully this question and answer will help me find this answer again next time I need it.