Thread: Multiple table relationship constraints
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 have come up with 4 possibilities. 1. Composite keys -- I could include all the attributes that must match on all the tables through the chain and let foreign key constraints handle it. This could work but it feels wrong to be duplicating attributes. It also is inconvenient (but possible) with my ORM. 2. Triggers -- I can use triggers to check every change on all 5 tables that could possibly cause an invalid chain. I have done this before and it does work -- but it can be error prone. 3. Check a materialized view -- Add triggers to all 5 tables to keep a materialized view up to date. Check constraints could validate the materialized view. 4. Validate application side -- this can work well, but it leaves the hole of a bug in the application or a direct SQL statement going bad. Anyone have any advice on the best way to handle this? -- Jack Christensen jackc@hylesanderson.edu
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
> -----Original Message----- > From: pgsql-general-owner@postgresql.org [mailto:pgsql-general- > owner@postgresql.org] On Behalf Of Jack Christensen > Sent: Thursday, May 05, 2011 3:20 PM > To: pgsql > Subject: [GENERAL] Multiple table relationship constraints > > 4. Validate application side -- this can work well, but it leaves the hole of a > bug in the application or a direct SQL statement going bad. > > > Anyone have any advice on the best way to handle this? > Not totally following the usage though I have come across similar requirements before. A variant of #4 would be to remove INSERT/UPDATE/DELETE permissions on the relevant tables and write SECURITY DEFINER functions to perform those actions instead. You can additionally leave the constraints loose and have the function query the tables post-modification to make sure they are still valid (kind of like the materialized view option but without a permanent table). David J.
On 5/5/2011 2:28 PM, Rick Genter wrote:
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.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
On Thu, May 5, 2011 at 3:50 PM, Jack Christensen <jackc@hylesanderson.edu> wrote:
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.genter@gmail.com
On 5/5/2011 2:53 PM, Rick Genter wrote:
Here's a contrived example:
CREATE TABLE dorms(
dorm_id serial PRIMARY KEY,
gender varchar NOT NULL,
...
);
CREATE TABLE people(
person_id serial PRIMARY KEY,
gender varchar NOT NULL,
...
);
CREATE TABLE room_assignments(
person_id integer NOT NULL REFERENCES people,
dorm_id integer NOT NULL REFERENCES dorms,
...
);
Men should only be assignable to men's dorms and women should only be assignable to women's dorms. On occasion a person's or dorm's gender needs to be updated. I want to make sure that doesn't cause a room assignment to become invalid. In this example, adding gender to room_assignments and using composite foreign keys is fairly straight forward -- but in my actual domain I have 5+ tables and 2+ attributes involved in the relationship.
It's not denormalized. It is an attribute that both tables have that have to match for it to be a valid link.On Thu, May 5, 2011 at 3:50 PM, Jack Christensen <jackc@hylesanderson.edu> wrote: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.So your data is denormalized? (The "category" appears in 2 tables?) Don't do that. Create a view that joins your two tables together instead if you need a single entity that contains data from multiple sources. Then you won't have any of the data integrity issues you're worried about.
Here's a contrived example:
CREATE TABLE dorms(
dorm_id serial PRIMARY KEY,
gender varchar NOT NULL,
...
);
CREATE TABLE people(
person_id serial PRIMARY KEY,
gender varchar NOT NULL,
...
);
CREATE TABLE room_assignments(
person_id integer NOT NULL REFERENCES people,
dorm_id integer NOT NULL REFERENCES dorms,
...
);
Men should only be assignable to men's dorms and women should only be assignable to women's dorms. On occasion a person's or dorm's gender needs to be updated. I want to make sure that doesn't cause a room assignment to become invalid. In this example, adding gender to room_assignments and using composite foreign keys is fairly straight forward -- but in my actual domain I have 5+ tables and 2+ attributes involved in the relationship.
--
Rick Genter
rick.genter@gmail.com
-- Jack Christensen jackc@hylesanderson.edu
On Thu, May 5, 2011 at 4:14 PM, Jack Christensen <jackc@hylesanderson.edu> wrote:
It's not denormalized. It is an attribute that both tables have that have to match for it to be a valid link.
Here's a contrived example:
CREATE TABLE dorms(
dorm_id serial PRIMARY KEY,
gender varchar NOT NULL,
...
);
CREATE TABLE people(
person_id serial PRIMARY KEY,
gender varchar NOT NULL,
...
);
CREATE TABLE room_assignments(
person_id integer NOT NULL REFERENCES people,
dorm_id integer NOT NULL REFERENCES dorms,
...
);
Men should only be assignable to men's dorms and women should only be assignable to women's dorms. On occasion a person's or dorm's gender needs to be updated. I want to make sure that doesn't cause a room assignment to become invalid. In this example, adding gender to room_assignments and using composite foreign keys is fairly straight forward -- but in my actual domain I have 5+ tables and 2+ attributes involved in the relationship.
Hm. I think the way I would handle this is to put the business logic for inserting/updating into the room_assignments table into one or more functions and have a special user that owns the tables and owns the functions and declare the functions to be SECURITY DEFINER. Revoke INSERT/UPDATE/DELETE access to the tables from all other users. Then you grant your regular users EXECUTE access to the functions. The functions run as the user that created them, so they will have direct INSERT/UPDATE/DELETE access to the tables while your regular users won't.
--
Rick Genter
rick.genter@gmail.com
I think the best way is what David has suggested...
But if it is already live, and there is no way to handle clients app to work with functions (instead of Direct SQL statements) then I think trigger function would help... (not sure how it could be error prone..)
So basically if function is
Validate(input parameters)
Insert/Update data
Inside trigger function call the same check Validation function(s) and then if it returns false - return NULL, otherwise return NEW
Kind Regards,
Misa
2011/5/5 David Johnston <polobo@yahoo.com>
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] On Behalf Of Jack Christensen
> Sent: Thursday, May 05, 2011 3:20 PM
> To: pgsql
> Subject: [GENERAL] Multiple table relationship constraints>Not totally following the usage though I have come across similar
> 4. Validate application side -- this can work well, but it leaves the hole
of a
> bug in the application or a direct SQL statement going bad.
>
>
> Anyone have any advice on the best way to handle this?
>
requirements before. A variant of #4 would be to remove
INSERT/UPDATE/DELETE permissions on the relevant tables and write SECURITY
DEFINER functions to perform those actions instead. You can additionally
leave the constraints loose and have the function query the tables
post-modification to make sure they are still valid (kind of like the
materialized view option but without a permanent table).
David J.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 5/5/2011 3:26 PM, Rick Genter wrote: > > Hm. I think the way I would handle this is to put the business logic > for inserting/updating into the room_assignments table into one or > more functions and have a special user that owns the tables and owns > the functions and declare the functions to be SECURITY DEFINER. Revoke > INSERT/UPDATE/DELETE access to the tables from all other users. Then > you grant your regular users EXECUTE access to the functions. The > functions run as the user that created them, so they will have direct > INSERT/UPDATE/DELETE access to the tables while your regular users won't. > Thanks everyone for your advice. I think this type of approach will be very helpful. -- Jack Christensen jackc@hylesanderson.edu