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

open
nobody
General (126)
5
2013-01-18
2005-09-26
Andy Dustman
No

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

Discussion

  • Andy Dustman
    Andy Dustman
    2005-10-31

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

     
  • nyenyec
    nyenyec
    2006-02-15

    Logged In: YES
    user_id=6142

    This is still a problem in sqlobject 0.7.1dev-r1457

     
  • L33tminion
    L33tminion
    2006-08-25

    Logged In: YES
    user_id=1583993

    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.