alternative/mutually exclusive foreign keys

[Julian Fowler]

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

 

[Jeff Boyce]

Julian

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

 

[Julian Fowler]

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

 

[Jeff Boyce]

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...