Validation Rule

[Tryandtry]

I am stumped with a question that I'm sure is easy for others in this
group.

I have an Access table with two fields: A and B.
A is the primary key.

I want to create a record-level validation rule prohibiting records
where A equals any B. In other words a join of A to B should come up
empty.

Can someone tell me how to do this? Here is the real kicker, I want to
implement this rule on the table, not the form. Is this possible?

Thanks!

Zac

 

[Allen Browne]

Use the Validation Rule of the table:

1. Open the table in design view.

2. Open the Properties box (View menu.)

3. Beside the Validation Rule in the Properties box, enter:
([A] <> ) OR ( Is Null)

Notes:
a) The Validation Rule of the table is different from the Validation Rule
that appears in the lower pane in table design. That one applies to a field,
not a record.

b) You can omit the OR if is a required field. You can also get away
without it in recent versions of Access. In older versions, it would have
had the effect of making B a required field, so I personally consider it
good practice to include it unless B is required.

 

[RoyVidar]

I am stumped with a question that I'm sure is easy for others in this
group.

I have an Access table with two fields: A and B.
A is the primary key.

I want to create a record-level validation rule prohibiting records
where A equals any B. In other words a join of A to B should come up
empty.

Can someone tell me how to do this? Here is the real kicker, I want
to implement this rule on the table, not the form. Is this possible?

Thanks!

Zac

If I understand you correct, I think you need to use methods not
available in the Access UI -> check constraints. This is a feature of
Jet 4.0, which isn't supported by Access. For more info, see the
following link (watch for linebreak in the link)
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/acintsql.asp

Probably the following might work

currentproject.connection.execute _
"ALTER TABLE myTable " & _
"ADD CONSTRAINT myTable_Check_A_differs_from_any_B " & _
"CHECK (A NOT IN (SELECT B FROM myTable))", , _
adcmdtext + adexecutenorecords

Note - if this is what you're after, then you cannot maintain this
through the Access UI, you need DDL. This might give you some
challenges...
To delete the table, you will probably need to either execute a
drop table DDL, or drop the check constraint explicitly first.

currentproject.connection.execute _
"ALTER TABLE myTable " & _
"DROP CONSTRAINT myTable_Check_A_differs_from_any_B ", , _
adcmdtext + adexecutenorecords

Likewise, if you need to copy/import/export this table to/from other
databases, you might see different behaviour with regards to whether
or not an error occurs, but no method, I think, will transfer the
check constraint, which I think you'll need to to manually.

 

[onedaywhen]

Probably the following might work

currentproject.connection.execute _
"ALTER TABLE myTable " & _
"ADD CONSTRAINT myTable_Check_A_differs_from_any_B " & _
"CHECK (A NOT IN (SELECT B FROM myTable))", , _
adcmdtext + adexecutenorecords

Good to see a CHECK constraint around here <g> but I don't think this
one does the job.

Consider:

CREATE TABLE myTable (
A INTEGER NOT NULL UNIQUE,
B INTEGER)
;
ALTER TABLE myTable ADD
CONSTRAINT myTable_Check_A_differs_from_any_B
CHECK (A NOT IN (SELECT B FROM myTable))
;
INSERT INTO myTable (A, B) VALUES (1, 2)
;
INSERT INTO myTable (A, B) VALUES (3, 1)
;

The CHECK does not bite after the second insert and the statement "A
differs from any B" is no longer true for the first inserted row. I
think that with the construt you've used the value of A only for the
inserted row(s) is checked.

This is the sort of constraint I'd write as an 'assertion' (the
resemblance to standard SQL's CREATE ASSERTION is not coincidental),
usually using a NOT EXISTS i.e. "there should never exist rows in this
state" kind of statement.

Consider:

DELETE FROM myTable
;
ALTER TABLE myTable DROP
CONSTRAINT myTable_Check_A_differs_from_any_B
;
ALTER TABLE myTable ADD
CONSTRAINT myTable_Check_A_differs_from_any_B
CHECK (
0 = (
SELECT *
FROM myTable AS T1, myTable AS T2
WHERE T1.A = T2.B))
;
INSERT INTO myTable (A, B) VALUES (1, 2)
;
INSERT INTO myTable (A, B) VALUES (3, 1)
;

This time the CHECK bites.

I originally used

ALTER TABLE myTable ADD
CONSTRAINT myTable_Check_A_differs_from_any_B CHECK (
NOT EXISTS (
SELECT *
FROM myTable AS T1, myTable AS T2
WHERE T1.A = T2.B))
;

but I get a funky 'No record' error when the CHECK is checked - pretty
useless, then.

The 0 = COUNT(*) approach is not preferred: NOT EXISTS should (I hope)
short circuit, plus COUNT with a nullable column takes some thought
hence is a maintenance issue (hint: COUNT(*)...GROUP BY will group and
count the null values).

Jamie.

--

 

[RoyVidar]

The CHECK does not bite after the second insert and the statement "A
differs from any B" is no longer true for the first inserted row. I
think that with the construt you've used the value of A only for the
inserted row(s) is checked.

Your'e correct, I don't know why that thought stuck, but it did.