alternative/mutually exclusive foreign keys

  • Thread starter Thread starter Julian Fowler
  • Start date Start date
J

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
 
Julian

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

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

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

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