alternative/mutually exclusive foreign keys

Part of a database I'm working on has three tables: City, State,
Country. For cities in the US I want to have a reference from City to
State (stateRef in the City table linked as foreign key to stateId in
State), whereas for cities in other countries I want to have a
reference from City to Country (countryRef linked as foreign key to
countryId in Country).

OK so far ... however, I want to be able to validate during data entry
that a City has a reference to a State *or* (exclusive) a reference to
a Country (i.e., that if stateRef is null, countryRef must not be
null, and vice versa). Any suggestions on how to accomplish this?

Julian

--
Julian Fowler
julian (at) bellevue-barn (dot) org (dot) uk

Julian

Seems like that would be easily handled in a form's BeforeUpdate event. You
ARE using forms for data entry, right?!

--
Good luck

Jeff Boyce
Access MVP

"Julian Fowler" wrote in message
...
Part of a database I'm working on has three tables: City, State,
Country. For cities in the US I want to have a reference from City to
State (stateRef in the City table linked as foreign key to stateId in
State), whereas for cities in other countries I want to have a
reference from City to Country (countryRef linked as foreign key to
countryId in Country).

OK so far ... however, I want to be able to validate during data entry
that a City has a reference to a State *or* (exclusive) a reference to
a Country (i.e., that if stateRef is null, countryRef must not be
null, and vice versa). Any suggestions on how to accomplish this?

Julian

--
Julian Fowler
julian (at) bellevue-barn (dot) org (dot) uk

On Thu, 24 Mar 2005 05:53:23 -0800, "Jeff Boyce"
-DISCARD_HYPHEN_TO_END wrote:

Julian

Seems like that would be easily handled in a form's BeforeUpdate event. You
ARE using forms for data entry, right?!

Of course :-) Events does seem an obvous way to go, although I was
hoping for something inherent in the definition of the
tables/relationships that would then apply in any relevant form.

Julian

--
Julian Fowler
julian (at) bellevue-barn (dot) org (dot) uk

Julian

It may be feasible to create a single validation rule to apply to the entire
table, but once you've used it for THIS situation, you won't get to create
any others. This is true of the Access/JET database, but not so for a
SQL-Server back-end.

I'd probably still go with the form events...

--
Good luck

Jeff Boyce
Access MVP

"Julian Fowler" wrote in message
...
On Thu, 24 Mar 2005 05:53:23 -0800, "Jeff Boyce"
-DISCARD_HYPHEN_TO_END wrote:

Julian

Seems like that would be easily handled in a form's BeforeUpdate event.
You
ARE using forms for data entry, right?!

Of course :-) Events does seem an obvous way to go, although I was
hoping for something inherent in the definition of the
tables/relationships that would then apply in any relevant form.

Julian

--
Julian Fowler
julian (at) bellevue-barn (dot) org (dot) uk