Disable constraint [CORE1924]

[firebird-automations]

Submitted by: @cincuranet

Votes: 14

Temporaly disabling constraints (FK, check, ...) using: ALTER TABLE <table> DISABLE <constraint> and ALTER TABLE <table> ENABLE <constraint>

This more or less similar to CORE1084, but with wider application.

[firebird-automations]

Commented by: Mark Stein (mark stein)

It would be great to have such a functionality!

I'm looking for a way do achieve that, but can't find anything.

This article: http://www.firebirdfaq.org/faq20/

says "[...]Alternatively, you can disable the constraints during the import and turn them back on later.[...]"

so it seems to be possible. I would like to know how.

Can anyone help me with this please?

If this is not possible yet, then please consider it for one of the next releases.

Thanks, Mark

[aafemt]

If standard-compliant ALTER TABLE ALTER CONSTRAINT [NOT] ENFORCED is implemented for PK/UK/FK, should equivalent ALTER TRIGGER INACTIVE to be allowed too? The same question for activation/deactivation of triggers for CHECK constraints.

[dyemanov]

If standard-compliant ALTER TABLE ALTER CONSTRAINT [NOT] ENFORCED is implemented for PK/UK/FK, should equivalent ALTER INDEX INACTIVE to be allowed too? The same question for activation/deactivation of triggers for CHECK constraints.

I'd prefer it raising an error and suggesting to use ALTER CONSTRAINT instead, but it will break backward compatibility.

If it's allowed, I'd expect the change to be propagated to the constraint using the altered index/trigger, as the state should be stored at the constraint level too and both states should be in sync.

[aafemt]

Why to duplicate the state? As any other denormalization it is just a source of troubles.

[dyemanov]

We're going to support FKs without indices, so the state should be stored at the constraint level.

[hvlad]

Not every kind of constraints requires index ever

[aafemt]

Ok, but let's wait until FKs without indices are implemented to see implementation details and if they don't have a place for "active" flag.

PS: Check constraints require triggers which also have own "inactive" flag. NULL constraints have corresponding flags in record format IIRC.