Re: Table property question
- From: "onedaywhen" <jamiecollins@xxxxxxxxxx>
- Date: 27 Mar 2007 03:39:23 -0700
On Mar 26, 8:06 pm, gmil...@xxxxxxxxxxxxxxxxxxxxxxxxx (G. Miller)
wrote:
I've got two fields in a
table named [SPW] and [NumPages]. I don't want the user to be able to
enter a SPW (single piece weight) without entering the number of
pages. Since Access doesn't allow me to compare table fields in the
fieldvalidationrules (ie, [NumPages] > [SPW]), I'm trying to do it
in the tablevalidationand according to everything I've read it's
supposed to work.....but doesn't.
I've got both fields as optional withvalidationrules of >0. In the
tablevalidation, I've simply got [NumPages] >= [SPW].
I assume by 'optional' you mean they can accept the NULL value and the
problem is the Validation Rule [NumPages] >= [SPW] is allowing a row
where NumPages is NULL and SPW is not NULL. The behaviour I've
described is correct and by design.
The behaviour was changed in Jet 4.0 (Access2000 and later) to be
compliant with CHECK constraints in the SQL-92 standard, which
specifies:
[Quote]
If the <table constraint> is a <check constraint definition>, then
let
SC be the <search condition> immediately contained in the <check
constraint definition> and let T be the table name included in the
corresponding table constraint descriptor; the table constraint is
not
satisfied if and only if
EXISTS ( SELECT * FROM T WHERE NOT ( SC ) )
is true.
[Unquote]
Read that again very carefully!
In a nutshell, SQL exhibits three value logic. Your Validation Rule
[NumPages] >= [SPW] can return three possible results: TRUE, FALSE and
UNKNOWN. In the case when NumPages is NULL and SPW equal 55 the
validation rule will evaluate NULL >= 55 and return UNKNOWN.
Should an UNKNOWN value fail a validation rule? The logic is, UNKNOWN
cannot be known to fail a rule so it is given the benefit of the doubt
and allowed to pass the rule. This behaviour is, I believe, for
pragmatic reasons: if you think about it, the alternative (i.e. a
Validation Rule must evaluate to TRUE) would require explicit tests
for NULL for every nullable column in validation rules and that would
quickly become tiresome.
It could well be the case that what you have read is wrong. Here's one
example I know of:
Access 2000 Project Guide
Chapter 2: Implementing Microsoft Access 2000 Client/Server Databases
http://msdn2.microsoft.com/en-us/library/aa139942(office.10).aspx
"When writing CHECK constraints, you should consider the following:
CHECK constraints are expressions that evaluate to either true or
false [wrong!], and only when the expressions evaluate to true are the
values accepted [wrong!] for the specified column or columns."
In conclusion: the concept of the NULL (data) value is tricky, many
people remain unaware of the concept of the UNKNOWN *logical* value
and do not realize there's a difference in behaviour between SQL DML
and SQL DDL. Perhaps the best approach is to alter your design to
remove the nullable columns. For inspiration, see:
How To Handle Missing Information Without Using NULL
By Hugh Darwen
http://www.dcs.warwick.ac.uk/~hugh/TTM/Missing-info-without-nulls.pdf
FWIW you can test the behaviour is correct for your Validation Rule as
regards the standards as follows (ANSI-92 Query Mode Access/Jet SQL):
CREATE TABLE Test (
NumPages INTEGER,
SPW INTEGER
)
;
INSERT INTO Test (NumPages, SPW)
VALUES (NULL, 55)
;
SELECT T1.NumPages, T1.SPW, EXISTS (
SELECT *
FROM Test
WHERE NOT ( NumPages >= SPW )
) AS validation_result
FROM Test AS T1
;
Remember, "the table constraint is not satisfied if [the result] is
true". In the above example, the 'result' column evaluates to FALSE
therefore the table constraint (Validation Rule) is satisfied for the
values used.
Jamie.
--
.
- References:
- Table property question
- From: G. Miller
- Table property question
- Prev by Date: Hiding access in the background, but still displaying reports and results of queries in a "front end"
- Next by Date: Why has my query suddenly gone read only
- Previous by thread: Re: Table property question
- Next by thread: Import different excel-files in one access-table
- Index(es):
Relevant Pages
|
