Re: ALTER TABLE ... ADD FOREIGN KEY ... NOT ENFORCED

On 12/12/2010 08:27 PM, Rob Wultsch wrote:
>
> MySQL does in fact have this feature and it is used by mysqldump. This
> feature is very useful.
>

The trouble is that FK's have more than one use. In particular, they 
have a documentary use that's used by tools that analyze databases, as 
well as by tools like htsql. They also have a role as an enforced 
constraint.

In fact it's possible now to disable FK enforcement, by disabling the 
triggers. It's definitely a footgun though. Just the other day I was 
asked how data violating the constraint could have got into the table, 
and caused some surprise by demonstrating how easy this was to produce.

So what would actually be an advance in my view would be a mechanism 
that allowed explicit disabling of a constraint but ensured that it was 
not violated when re-enabling it.

cheers

andrew