J
Jamie Collins
And the case against:
1) CHECK constraints cannot be created, edited or deleted using the
Access graphical user interface. They can only be created via Jet SQL
DDL statements. Once created, they do not show at all in the table
Design View.
2) CHECK constraints are at odds with standard Access behaviour. While
CHECK constraints provide a means of allowing multiple field- and
record-level Validation Rules, engine-level validation in general is an
inconvenience for 'bound' forms, where form-level validation using VBA
is usually preferred.
3) It is not possible to associate an engine-level custom failure
message (Validation Text) with a CHECK constraint. The best one can do
is to assign a meaningful name to the constraint e.g.
earnings__end_date__must_be_later_than_start_date
that can be caught in error handling code in the front end e.g.
If InStr(1, Err.Description, CONSTRAINT_NAME) > 0 Then ...
to be replaced by a more user-friendly message.
4) A table containing a CHECK constraint cannot be pasted or imported
using the Access interface. Attempting to do so in Access 2007
generates the following warning: "CHECK constraints on table <<table
name>> will not be transferred to this table. CHECK constraints can
only be created via SQL DDL statements."
5) There is a bug in the Jet 4.0/Access 7.0 engine where the message
that shows when attempting to delete or drop a table that has an
associated table-level CHECK constraint fails to include the constraint
name.
CREATE TABLE Test (
data_col INTEGER,
CONSTRAINT max_two_duplicates
CHECK (NOT EXISTS (
SELECT T2.data_col
FROM Test AS T2
GROUP BY T2.data_col
HAVING COUNT(*) > 2
))
)
;
DROP TABLE Test
;
The error message reads (verbatim):
"DDL cannot be completed on this table because it is referenced by
constraint on table ."
The spaces in the message indicate where the missing table name and
constraint name should be.
Arguably, the DROP TABLE command should automatically drop the
constraint in the same process, as happens for all other table
constraints, negating the need for such a message.
6) Executing a CREATE TABLE or ALTER TABLE command using a CHECK
constraint clause which Jet does not like causes both Jet and the host
application to crash.
Here is some SQL to reproduce at your peril (e.g. using an ADO
connection):
CREATE TABLE EarningsHistory (
employee_nbr VARCHAR(11) NOT NULL,
start_date DATETIME NOT NULL,
end_date DATETIME,
salary_amount CURRENCY NOT NULL
);
-- WARNING:
-- EXECUTING THE NEXT SQL STATEMENT
-- COULD CRASH YOUR APPLICATION!!
ALTER TABLE EarningsHistory ADD
CONSTRAINT earnings_history__no_overlapping_periods
CHECK (NOT EXISTS (
SELECT *
FROM EarningsHistory, EarningsHistory AS E2
WHERE EarningsHistory.employee_nbr = E2.employee_nbr
AND EarningsHistory.start_date < E2.start_date
AND
(
E2.start_date
< IIF(EarningsHistory.end_date IS NULL, NOW(),
EarningsHistory.end_date)
OR IIF(E2.end_date IS NULL,
NOW(),
E2.end_date) < IIF(EarningsHistory.end_date IS NULL,
NOW(), EarningsHistory.end_date)
))
);
What causes Jet's aversion is unknown: similar CHECK definitions using
NOT EXISTS do not crash; other CHECK definitions that are similarly
'possibly not-deterministic' do not crash. The CHECK clause can cause a
crash regardless of whether it is syntactically-correct or otherwise.
Changing the above CHECK definition to use a count will prevent the
crash i.e.
CHECK (0 = (
SELECT COUNT(*)
FROM ...))
Note I have not been able to reproduce this bug in Access 2007, either
from a query's SQL view while in ANSI-92 query mode or via
CurrentProject.Connection.Execute. This could mean the bug has been
fixed for the Access 2007 engine or does not apply to the Access
interface at all; however, I cannot conclude that the bug will not be
encountered in Access 2007 or any other version of Access.
7) Jet's CHECK constraint implementation is non-compliant with the ANSI
SQL-92 standard as regards the Information Schema:
<quote>
If the character representation of the <search condition> cannot be
represented in the Information Schema without truncation, then a
completion condition is raised: warning-search condition too long for
information schema.
</quote>
X3H2-92-154/DBL CBR-002 [ANSI SQL-92 specification], 11.9 <check
constraint definition>
Where a check clause exceeds 255 characters, Jet replaces it with the
null value, without warning. To test:
Sub CHECK_clause_limit()
Kill "C:\DropMe.mdb"
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\DropMe.mdb"
With .ActiveConnection
' Create auxilary table of digits 0-9
.Execute _
"CREATE TABLE Test (data_col INTEGER NOT NULL);"
Const CHECK_TEXT As String = "1 = (SELECT COUNT(*) FROM Test AS T2
WHERE T2.data_col = 1 OR T2.data_col = 2 OR T2.data_col = 3 OR
T2.data_col = 4 OR T2.data_col = 5 OR T2.data_col = 6 OR T2.data_col =
7 OR T2.data_col = 8 OR T2.data_col = 9 OR T2.data_col = 10 OR
T2.data_col = 11 OR T2.data_col = 12 OR T2.data_col = 13 OR T2.data_col
= 14 OR T2.data_col = 15)"
.Execute _
"ALTER TABLE Test ADD CONSTRAINT ck__test CHECK (" & CHECK_TEXT &
");"
Dim rs As ADODB.Recordset
Set rs = .OpenSchema(adSchemaCheckConstraints) ', Array(Empty,
Empty, "ck__test"))
rs.Filter = "CONSTRAINT_NAME = 'ck__test'"
MsgBox _
"Length of CHECK clause: " & CStr(Len(CHECK_TEXT)) & vbCr & _
"Length of CHECK clause column from the Information Schema: " &
CStr(rs.Fields("CHECK_CLAUSE").DefinedSize) & vbCr & _
"CHECK clause text from the Information Schema: " &
IIf(IsNull(rs.Fields("CHECK_CLAUSE").Value), "{{NULL}}",
rs.Fields("CHECK_CLAUSE").Value)
End With
Set .ActiveConnection = Nothing
End With
End Sub
8) Jet CHECK constraints are not deferrable. While the same is true of
all other Jet constraints, it is particularly obstructive in the case
of CHECK constraints. Take the example of a valid-time state table
('history table'), where a CHECK constraint is required to implement
the sequenced uniqueness constraint (i.e. primary key to ensure periods
do not overlap). Considering a single sequenced deletion is implemented
by an insertion, two updates and a deletion, in that order, the
sequenced primary key would be temporarily violated by the INSERT.
Without the ability to defer the constraint, it must be dropped and
later recreated, all within a serializable transaction. This has
obvious implications as regards concurrency. There is also a SQL DDL
code maintenance issue: consider when the CHECK clause cannot be
retrieved from the Information Schema (see 255 character limit above)
it may not be acceptable to persist the clause within the front end
code.
--
Jet CHECK constraints in summary: if you do manage to create a CHECK
constraint using Jet SQL DLL without the damn thing crashing, can trust
(rather than see) it still exists from minute to minute, and can live
with table locks while constraints are 'deferred' and ugly error
messages when the constraint bites, you'd better just hope that the
table never needs to be ported, because the Access interface can't do
it for you, or otherwise maintained without the documentation to hand
because Jet cannot tell you the CHECK clause if it is longer than 255
characters and won't always reveal the constraint name.
Jamie.
--
1) CHECK constraints cannot be created, edited or deleted using the
Access graphical user interface. They can only be created via Jet SQL
DDL statements. Once created, they do not show at all in the table
Design View.
2) CHECK constraints are at odds with standard Access behaviour. While
CHECK constraints provide a means of allowing multiple field- and
record-level Validation Rules, engine-level validation in general is an
inconvenience for 'bound' forms, where form-level validation using VBA
is usually preferred.
3) It is not possible to associate an engine-level custom failure
message (Validation Text) with a CHECK constraint. The best one can do
is to assign a meaningful name to the constraint e.g.
earnings__end_date__must_be_later_than_start_date
that can be caught in error handling code in the front end e.g.
If InStr(1, Err.Description, CONSTRAINT_NAME) > 0 Then ...
to be replaced by a more user-friendly message.
4) A table containing a CHECK constraint cannot be pasted or imported
using the Access interface. Attempting to do so in Access 2007
generates the following warning: "CHECK constraints on table <<table
name>> will not be transferred to this table. CHECK constraints can
only be created via SQL DDL statements."
5) There is a bug in the Jet 4.0/Access 7.0 engine where the message
that shows when attempting to delete or drop a table that has an
associated table-level CHECK constraint fails to include the constraint
name.
CREATE TABLE Test (
data_col INTEGER,
CONSTRAINT max_two_duplicates
CHECK (NOT EXISTS (
SELECT T2.data_col
FROM Test AS T2
GROUP BY T2.data_col
HAVING COUNT(*) > 2
))
)
;
DROP TABLE Test
;
The error message reads (verbatim):
"DDL cannot be completed on this table because it is referenced by
constraint on table ."
The spaces in the message indicate where the missing table name and
constraint name should be.
Arguably, the DROP TABLE command should automatically drop the
constraint in the same process, as happens for all other table
constraints, negating the need for such a message.
6) Executing a CREATE TABLE or ALTER TABLE command using a CHECK
constraint clause which Jet does not like causes both Jet and the host
application to crash.
Here is some SQL to reproduce at your peril (e.g. using an ADO
connection):
CREATE TABLE EarningsHistory (
employee_nbr VARCHAR(11) NOT NULL,
start_date DATETIME NOT NULL,
end_date DATETIME,
salary_amount CURRENCY NOT NULL
);
-- WARNING:
-- EXECUTING THE NEXT SQL STATEMENT
-- COULD CRASH YOUR APPLICATION!!
ALTER TABLE EarningsHistory ADD
CONSTRAINT earnings_history__no_overlapping_periods
CHECK (NOT EXISTS (
SELECT *
FROM EarningsHistory, EarningsHistory AS E2
WHERE EarningsHistory.employee_nbr = E2.employee_nbr
AND EarningsHistory.start_date < E2.start_date
AND
(
E2.start_date
< IIF(EarningsHistory.end_date IS NULL, NOW(),
EarningsHistory.end_date)
OR IIF(E2.end_date IS NULL,
NOW(),
E2.end_date) < IIF(EarningsHistory.end_date IS NULL,
NOW(), EarningsHistory.end_date)
))
);
What causes Jet's aversion is unknown: similar CHECK definitions using
NOT EXISTS do not crash; other CHECK definitions that are similarly
'possibly not-deterministic' do not crash. The CHECK clause can cause a
crash regardless of whether it is syntactically-correct or otherwise.
Changing the above CHECK definition to use a count will prevent the
crash i.e.
CHECK (0 = (
SELECT COUNT(*)
FROM ...))
Note I have not been able to reproduce this bug in Access 2007, either
from a query's SQL view while in ANSI-92 query mode or via
CurrentProject.Connection.Execute. This could mean the bug has been
fixed for the Access 2007 engine or does not apply to the Access
interface at all; however, I cannot conclude that the bug will not be
encountered in Access 2007 or any other version of Access.
7) Jet's CHECK constraint implementation is non-compliant with the ANSI
SQL-92 standard as regards the Information Schema:
<quote>
If the character representation of the <search condition> cannot be
represented in the Information Schema without truncation, then a
completion condition is raised: warning-search condition too long for
information schema.
</quote>
X3H2-92-154/DBL CBR-002 [ANSI SQL-92 specification], 11.9 <check
constraint definition>
Where a check clause exceeds 255 characters, Jet replaces it with the
null value, without warning. To test:
Sub CHECK_clause_limit()
Kill "C:\DropMe.mdb"
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\DropMe.mdb"
With .ActiveConnection
' Create auxilary table of digits 0-9
.Execute _
"CREATE TABLE Test (data_col INTEGER NOT NULL);"
Const CHECK_TEXT As String = "1 = (SELECT COUNT(*) FROM Test AS T2
WHERE T2.data_col = 1 OR T2.data_col = 2 OR T2.data_col = 3 OR
T2.data_col = 4 OR T2.data_col = 5 OR T2.data_col = 6 OR T2.data_col =
7 OR T2.data_col = 8 OR T2.data_col = 9 OR T2.data_col = 10 OR
T2.data_col = 11 OR T2.data_col = 12 OR T2.data_col = 13 OR T2.data_col
= 14 OR T2.data_col = 15)"
.Execute _
"ALTER TABLE Test ADD CONSTRAINT ck__test CHECK (" & CHECK_TEXT &
");"
Dim rs As ADODB.Recordset
Set rs = .OpenSchema(adSchemaCheckConstraints) ', Array(Empty,
Empty, "ck__test"))
rs.Filter = "CONSTRAINT_NAME = 'ck__test'"
MsgBox _
"Length of CHECK clause: " & CStr(Len(CHECK_TEXT)) & vbCr & _
"Length of CHECK clause column from the Information Schema: " &
CStr(rs.Fields("CHECK_CLAUSE").DefinedSize) & vbCr & _
"CHECK clause text from the Information Schema: " &
IIf(IsNull(rs.Fields("CHECK_CLAUSE").Value), "{{NULL}}",
rs.Fields("CHECK_CLAUSE").Value)
End With
Set .ActiveConnection = Nothing
End With
End Sub
8) Jet CHECK constraints are not deferrable. While the same is true of
all other Jet constraints, it is particularly obstructive in the case
of CHECK constraints. Take the example of a valid-time state table
('history table'), where a CHECK constraint is required to implement
the sequenced uniqueness constraint (i.e. primary key to ensure periods
do not overlap). Considering a single sequenced deletion is implemented
by an insertion, two updates and a deletion, in that order, the
sequenced primary key would be temporarily violated by the INSERT.
Without the ability to defer the constraint, it must be dropped and
later recreated, all within a serializable transaction. This has
obvious implications as regards concurrency. There is also a SQL DDL
code maintenance issue: consider when the CHECK clause cannot be
retrieved from the Information Schema (see 255 character limit above)
it may not be acceptable to persist the clause within the front end
code.
--
Jet CHECK constraints in summary: if you do manage to create a CHECK
constraint using Jet SQL DLL without the damn thing crashing, can trust
(rather than see) it still exists from minute to minute, and can live
with table locks while constraints are 'deferred' and ugly error
messages when the constraint bites, you'd better just hope that the
table never needs to be ported, because the Access interface can't do
it for you, or otherwise maintained without the documentation to hand
because Jet cannot tell you the CHECK clause if it is longer than 255
characters and won't always reveal the constraint name.
Jamie.
--