1) I'm not sure what you mean by C1.'Max_score' in the WHERE clause and
what's reason for the Cartesian join. ISTM that this does the job:
ALTER TABLE tblRON ADD
CONSTRAINT Exceeds_maximum_score
CHECK (
NOT EXISTS (
SELECT *
FROM tblRON
WHERE Score > (SELECT C_Value FROM tblConstraints
WHERE C_Label = 'Max_Score')
)
)
Sorry about that. My aircode was a 'cut and paste' based on your code
and I assumed Max_Score was an attribute rather than a data element.
Now that I see the giveaway quotes in your code, I wonder whether you
are committing the OTLT design flaw (google it).
I'd normally see a single row table and changing you design
accordingly, your above revision is semantically equivalent to mine so
it's the same difference. If yours makes more sense to you then that's
good because you'll find the code easier to maintain. Mine suits me
and is based on the presumption that the will optimizer prefer a
straightforward join to its subquery equivalent (but I wouldn't be
surprised if their respective 'execution plans' are identical).
Another presumption is the engine won't go far down the road of
returning a large resultset because it only takes one row to fail a
NOT EXISTS clause!
2) How would one modify this constraint to allow Null scores?
I think I see that the Cartesian join ensures that the value of Score is
always checked in all records
No, I actually think it's due to a bug. I think my CHECK constraint is
designed to handle the NULL value correctly and think that the engine
isn't. That's quite a bold statement so allow me to demonstrate with
some sample data.
Ignore for the moment that the usage for tblRON here is mightily
contrived:
CREATE TABLE tblConstraints (
lock CHAR(1) DEFAULT 'x' NOT NULL PRIMARY KEY,
CONSTRAINT tblConstraints__max_one_row
CHECK (lock = 'x'),
Max_score INTEGER NOT NULL
)
;
INSERT INTO tblConstraints (Max_score) VALUES (10)
;
CREATE TABLE tblRON (
player_name VARCHAR(15) NOT NULL UNIQUE,
Score INTEGER
)
;
I'm not creating the constraint at this time and instead adding some
test data:
INSERT INTO tblRON (player_name, Score) VALUES ('Rick', NULL)
;
Now try the query intended for the constraint:
SELECT *
FROM tblConstraints AS C1, tblRON AS I1
WHERE I1.Score > C1.Max_score
It returns an empty set, therefore a row with the NULL value for Score
should not fail the constraint. Now let's test it for real:
DELETE FROM tblRON
;
ALTER TABLE tblRON ADD
CONSTRAINT Exceeds_maximum_score
CHECK (
NOT EXISTS (
SELECT *
FROM tblConstraints AS C1, tblRON AS I1
WHERE I1.Score > C1.Max_score
)
)
;
INSERT INTO tblRON (player_name, Score) VALUES ('Rick', NULL)
;
Oh dear, the CHECK constraint bites when it should not! A blatant bug
and one with little hope of being fixed, I'm afraid. Note your
construct also erroneously chokes on the NULL value, it's nothing to
do with the join type.
Adding a 'hint' to the WHERE clause seems to do the trick:
SELECT *
FROM tblConstraints AS C1, tblRON AS I1
WHERE I1.Score > C1.Max_score
AND I1.Score IS NOT NULL
but that strikes me as a bit daft and I'm more inclined to conclude
the engine is not fit for purpose, considering the rate at which such
issues are addressed
As regards handling the NULL value, to be honest I have so few columns
that accept the NULL value that I hardly ever need to bother with them
in constraints (a win:win situation <g>!) I pointed out the above
example was contrived because it strikes me that if the player doesn't
yet have a score the they should be entered into the table and if the
was no other way of recording a player then I'd be concerned about
delete anomalies (redundancy) in the model. For inspiration on how to
avoid such situations, see:
How To handle Missing Information Without Using NULL
http://www.dcs.warwick.ac.uk/~hugh/TTM/Missing-info-without-nulls.pdf
FWIW I don't think it's a Cartesian join (FWIW more lately know as
CROSS JOIN as per the SQL-92 standard). Because it has a join
condition, I'd consider it an INNER JOIN e.g. this is semantically
equivalent again:
SELECT *
FROM tblConstraints AS C1
INNER JOIN tblRON AS I1
ON I1.Score > C1.Max_score
For an INNER JOIN, a search condition (WHERE) and a join condition
(ON) should be interchangeable but Access/Jet is fussy about certain
join conditions e.g. it wouldn't like this:
SELECT *
FROM tblConstraints AS C1
INNER JOIN tblRON AS I1
ON I1.Score NOT BETWEEN C1.Min_score AND C1.Max_score
but would be fine with this:
SELECT *
FROM tblConstraints AS C1, tblRON AS I1
WHERE I1.Score NOT BETWEEN C1.Min_score AND C1.Max_score
So I guess I've got into the habit of favouring a search condition
too
Probably there's documentation at msdn.microsoft.com, but I haven't yet
got round to searching for it.
I've looked and I really think there is nothing to see. I find it
quite surprising that this whole area of functionality is
undocumented. Remember, CHECK constraints are mentioned as a feature
of Access/Jet therefore they are supported (whatever that means). My
theory is, they think that if they don't tell us what it's supposed to
do then we can't find bugs with it. My stance is, if they don't tell
us what it's supposed to do then they should be compliant with the
SQL-92 standard, which is very detailed about how table constraints
should work (and they are not compliant as regards timing of checks
but that's a whole other post).
With all that in mind, here's a suggestion on how to avoid the table-
level CHECK constraint by instead using declarative referential
integrity. Something like this:
CREATE TABLE tblConstraints (
lock CHAR(1) DEFAULT 'x' NOT NULL PRIMARY KEY,
CONSTRAINT tblConstraints__max_one_row CHECK (lock = 'x'),
Max_score INTEGER NOT NULL UNIQUE
)
;
CREATE TABLE tblRON (
player_name VARCHAR(15) NOT NULL UNIQUE,
Score INTEGER,
Max_score INTEGER NOT NULL
REFERENCES tblConstraints (Max_score)
ON UPDATE CASCADE,
CONSTRAINT Exceeds_maximum_score CHECK (Score <= Max_score)
)
;
INSERT INTO tblConstraints (Max_score) VALUES (10)
;
INSERT INTO tblRON (player_name, Score, Max_score)
SELECT 'Jamie', 3, Max_score
FROM tblConstraints
;
INSERT INTO tblRON (player_name, Score, Max_score)
SELECT 'Rick', NULL, Max_score
FROM tblConstraints
;
INSERT INTO tblRON (player_name, Score, Max_score)
SELECT 'John', 99, Max_score
FROM tblConstraints
;
Note only the final INSERT makes the constraint bite and that the NULL
is handled properly (i.e. allowed in the above scenario). The CHECK in
this case could be replaced by a record-level Validation Rule.
Jamie.
--
