Re: Multiple table relationship constraints

On 5/5/2011 2:28 PM, Rick Genter wrote:
> On Thu, May 5, 2011 at 3:20 PM, Jack Christensen
> <jackc(at)hylesanderson(dot)edu <mailto: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...)
The trick is there are additional attributes of actions and achievements
such as a category that must match for the link to be valid. These
attributes are not part of the primary key of either record and can and
do change.
> --
> Rick Genter
> rick(dot)genter(at)gmail(dot)com <mailto:rick(dot)genter(at)gmail(dot)com>
>

--
Jack Christensen
jackc(at)hylesanderson(dot)edu