#115 0.7a: RelatedJoin() glue table has no constraints

General (125)

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).


  • Andy Dustman

    Andy Dustman - 2005-10-31

    Logged In: YES

    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().

  • nyenyec

    nyenyec - 2006-02-15

    Logged In: YES

    This is still a problem in sqlobject 0.7.1dev-r1457

  • L33tminion

    L33tminion - 2006-08-25

    Logged In: YES

    At the very least, adding ON DELETE CASCADE, etc., to the
    join table should be an option. Working around that is
    _really_ annoying.

    I second adustman's comments.


Log in to post a comment.

Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:

JavaScript is required for this form.

No, thanks