Re: Multiple table relationship constraints

On Thu, May 5, 2011 at 3:20 PM, Jack Christensen <jackc@hylesanderson.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.genter@gmail.com

-- 
Jack Christensen
jackc@hylesanderson.edu