> When we decided not to validate constraints on declaration, our reasoning > was that computations and database access were expensive and any decent > application programmer or DBA would always validate constraints before > declaring them and control access to the constrained items until the > constraint was successfully committed. > > That was then. Now, well, cycles are a lot easier to come by than good > developers. > I think it would be wise to add a [NO] VALIDATION modifier to constraint > definitions, including NOT NULL and referential integrity definitions. In > the presence of a VALIDATION modifier, Firebird would begin enforcing the > constraint on commit (as now) and then start a pass to insure that the data > complies with the constraint. I'd also be tempted to add a database > configuration option that makes validation the default.
I support this proposition with following additions: - every constraint could be ACTIVE or INACTIVE ACTIVE constraint are checked when data entered, INACTIVE constraint are not checked, but present in database dictionary - every constraint could be marked as VALIDATED or NOT VALIDATED by the engine - when constraint is INACTIVE it can't be marked as VALIDATED - when constraint is ACTIVE it can be marked as VALIDATED or marked as NOT VALIDATED, in later case it is up to sysdba to validate it at some moment - there should be SQL clauses to [IN]ACTIVATE and VALIDATE constraint, both for CREATE CONSTRAINT and for ALTER CONSTRAINT statements - if constraint is created in ACTIVE state and VALIDATE is specified, engine should validate constraint at commit time, if such validation failed, engine should leave constraint in dictionary but mark it as NOT VALIDATED. Of course, some warning should be put into status-vector - restore could create all constraints without validation (for speed) and later validate its explicitly (if not specified -no_validate switch) - we could make special kind of ALTER TABLE statement to validate few constraints at one pass, or tweak DFW to make it validate constrains for the same table at one pass Regards, Vlad ------------------------------------------------------------------------------ Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel