[SQL-CVS] [ sqlobject-Bugs-1305062 ] 0.7a: RelatedJoin() glue table has no constraints

[SourceForge.net <no...@so...>]

Bugs item #1305062, was opened at 2005-09-26 13:07
Message generated for change (Comment added) made by adustman
You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=540672&aid=1305062&group_id=74338

Please note that this message will contain a full copy of the comment thread,
including the initial issue submission, for this request,
not just the latest update.
Category: General
Group: SQLObject release (specify)
Status: Open
Resolution: None
Priority: 5
Submitted By: Andy Dustman (adustman)
Assigned to: Nobody/Anonymous (nobody)
Summary: 0.7a: RelatedJoin() glue table has no constraints

Initial Comment:
Consider the example from the online 0.6.1
documentation, with the User and Role objects, where
each object does a RelatedJoin() to the other. This
results in a glue table that looks like this:

CREATE TABLE user_role (
user_id INT NOT NULL,
role_id INT NOT NULL
)

The schema lacks any FOREIGN KEY constraints, and a
PRIMARY KEY. It should probalby look more like this:

CREATE TABLE user_role (
user_id INT NOT NULL, CONSTRAINT user_id_exists FOREIGN
KEY (user_id) REFERENCES user (id),
role_id INT NOT NULL, CONSTRAINT role_id_exists FOREIGN
KEY (role_id) REFERENCES role (id),
PRIMARY KEY (user_id, role_id)
)

ON DELETE CASCADE may be needed as well.

Note that I am using patch #1299109 to add FOREIGN KEY
support for MySQL. However, looking at join.py, it does
not seem to do anything with foreign keys, so I do not
think this is a dependency on MySQL; I think this
behavior should be reproducable on other databases. The
intermediate table schema is created in
dbconnection.DBAPI._SO_createJoinTableSQL(), and it
calls self.joinSQLType(join) to get the SQL for the
column. In all the implementations (except Sybase which
is slightly different), it returns 'INT NOT NULL'

The table creation order looks like it could be a
problem, as the glue/intermediate table seems to be
created before the second of the other two tables,
which would produce referential integrity problems
(i.e.reference to a non-existent table).

----------------------------------------------------------------------

>Comment By: Andy Dustman (adustman)
Date: 2005-10-31 17:34

Message:
Logged In: YES 
user_id=71372

BTW, the table creation order comment is really more of a
problem for Turbogears.

I've done a bit of testing by altering an existing glue
table to use ON DELETE CASCADE and it seems to work as
expected/desired. Without ON DELETE CASCADE, you have to
delete the glue entry tables manually, (typically with
obj.removeXXX(other_id)); otherwise your glue table has a
bunch of dead entries, though if you have the FOREIGN KEY
entry without ON CASCADE DELETE, then you can't remove the
parent objects without first deleting the glue records.

In other words, having FOREIGN KEY ... REFERENCES ... ON
DELETE CASCADE keeps your glue table clean when you remove
either object of the RelatedJoin().

----------------------------------------------------------------------

You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=540672&aid=1305062&group_id=74338