Re: Multiple table relationship constraints

On Thu, May 5, 2011 at 3:20 PM, Jack Christensen <jackc(at)hylesanderson(dot)edu>wrote:

> What is the best way to handle multiple table relationships where
> attributes of the tables at the ends of the chain must match?
>
> Example:
>
> CREATE TABLE achievements(
> achievement_id serial PRIMARY KEY,
> ...
> );
>
> CREATE TABLE achievement_versions(
> achievement_version_id serial PRIMARY KEY,
> achievement_id integer NOT NULL REFERENCES achievements,
> ...
> );
>
> CREATE TABLE achievement_attempts(
> achievement_attempt_id serial PRIMARY KEY,
> achievement_version_id integer NOT NULL REFERENCES achievement_versions,
> ...
> );
>
> CREATE TABLE actions(
> action_id serial PRIMARY KEY,
> ...
> )
>
> CREATE TABLE achievement_attempt_actions(
> achievement_attempt_id integer NOT NULL REFERENCES achievement_attempts,
> action_id integer NOT NULL REFERENCES actions,
> PRIMARY KEY( achievement_attempt_id, action_id)
> );
>
>
> The achievement_attempt_actions table links actions to
> achievement_attempts. For a link to be valid a number of attributes of
> actions must match attributes of achievements and achievement_attempts. This
> means an update to any of these 5 tables could invalidate the chain. How can
> I eliminate the possibility for this type of erroneous data?
>

I might not be understanding your question, but isn't that what your foreign
key references do? For example, you can't update achievement_attempt_id in
the achievement_attempt table if there is an achievement_attempt_actions
record that refers to it since that would break the reference. (Not that you
want to be updating primary key values in the first place...)
--
Rick Genter
rick(dot)genter(at)gmail(dot)com