MySQL Bugs: #2144: Constraints lack the "enabled" attribute.

Bug #2144	Constraints lack the "enabled" attribute.
Submitted:	17 Dec 2003 13:26	Modified:	28 Nov 2005 8:56
Reporter:	Jeff Holt	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S4 (Feature request)
Version:	all	OS:	Any (all)
Assigned to:		CPU Architecture:	Any

Description:
You give a good argument for not enforcing constraints. The problem is that you forgot to let the DBA determine whether or not a constraint (enumeration or whatever) should be enabled.

How to repeat:
There are no repeat instructions because it's not really a bug in the strictest sense. In an academic sense, it's a bug because you could have given constraints the ability to be address by name and given them an "enabled" attribute.

I think that my suggested fix shows how constraints could have been implemented with more flexibility as soon as transactional tables were supported.

Suggested fix:
If a table is created as non-transactional, then all constraints are added with enabled=false and therefore the current behavior (non-enforcement) is maintained.disabled constraint is added and the current behavior (non-enforcement) is maintained.

Otherwise, the constraint is added with enabled=true and it is enforced.

You can easily imagine how various alter commands might function to maintain current behavior. Even the alter command that changes a table from transactional to non-transactional and vica-versa.

I would suggest that an alteration of a table's transactional status should leave all constraints disabled. And then force the DBA to issue 'enabling' alter commands if the constraints can--and should--be enabled.

Changing this to a feature request so that it can be handled as such.

Thank you for a feature request. I agree that constraint state ("enabled"/"disabled") should be tracked in our "data dictionary". A separate column can be added to the existing INFORMATION_SCHEMA.TABLE_CONSTRAINTS table (http://dev.mysql.com/doc/refman/5.0/en/table-constraints-table.html) just for that purpose.