Re: Multiple table relationship constraints

From: Rick Genter <rick(dot)genter(at)gmail(dot)com>
To: Jack Christensen <jackc(at)hylesanderson(dot)edu>
Cc: pgsql <pgsql-general(at)postgresql(dot)org>, rick(dot)genter(at)gmail(dot)com
Subject: Re: Multiple table relationship constraints
Date: 2011-05-05 19:28:22
Message-ID: BANLkTikCEt=icmgmh_92koWP0krTCp4Zvg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Rick Chu 2011-05-05 19:36:39 Re: psql tunneling thru a middle server
Previous Message Jack Christensen 2011-05-05 19:20:17 Multiple table relationship constraints