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