[SQL-CVS] [ sqlobject-Bugs-1432190 ] Table creation order for foreign key constraints

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

Bugs item #1432190, was opened at 2006-02-15 15:22
Message generated for change (Tracker Item Submitted) made by Item Submitter
You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=540672&aid=1432190&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: None
Group: None
Status: Open
Resolution: None
Priority: 5
Submitted By: Björn Lindqvist (sonderblade)
Assigned to: Nobody/Anonymous (nobody)
Summary: Table creation order for foreign key constraints

Initial Comment:
Note: I had to apply my patch in
https://sourceforge.net/tracker/index.php?func=detail&aid=1432176&group_id=74338&atid=540674
otherwise the foreign key constrains would be
completely ignored. I have a simple model.py file
containing my two tables like this:

    class Network(SQLObject):
        name = StringCol(alternateID = True, length = 80)

    class Channel(SQLObject):
        network = ForeignKey("Network", notNull = True)
        name = StringCol()

Using "tg-admin sql sql" produces a listing like this:

CREATE TABLE channel (
    id INT PRIMARY KEY AUTO_INCREMENT,
    network_id INT NOT NULL,
    name TEXT
);

CREATE TABLE network (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(80) NOT NULL UNIQUE
);

ALTER TABLE channel ADD CONSTRAINT
channel_network_id_exists FOREIGN KEY (network_id)
REFERENCES network (id);

Creating these two tables manually work:

    Network.createTable()
    Channel.createTable()

But when I try to create them using "tg-admin sql
create" (which just wraps "sqlobject-admin create") I
get the following exception:

Traceback (most recent call last):
  File "/usr/bin/tg-admin", line 7, in ?
    sys.exit(
  File
"/usr/lib/python2.4/site-packages/TurboGears-0.9a0dev_r778-py2.4.egg/turbogears/command/base.py",
line 269, in main
    command.run()
  File
"/usr/lib/python2.4/site-packages/TurboGears-0.9a0dev_r778-py2.4.egg/turbogears/command/base.py",
line 129, in run
    command.the_runner.run(sys.argv)
  File
"/usr/lib/python2.4/site-packages/SQLObject-0.8dev_r1596-py2.4.egg/sqlobject/manager/command.py",
line 100, in run
    runner.run()
  File
"/usr/lib/python2.4/site-packages/SQLObject-0.8dev_r1596-py2.4.egg/sqlobject/manager/command.py",
line 231, in run
    self.command()
  File
"/usr/lib/python2.4/site-packages/SQLObject-0.8dev_r1596-py2.4.egg/sqlobject/manager/command.py",
line 583, in command
    tableConstraints = soClass.createTable()
  File
"/usr/lib/python2.4/site-packages/SQLObject-0.8dev_r1596-py2.4.egg/sqlobject/main.py",
line 1372, in createTable
    conn.query(constraint)
  File
"/usr/lib/python2.4/site-packages/SQLObject-0.8dev_r1596-py2.4.egg/sqlobject/dbconnection.py",
line 314, in query
    return self._runWithConnection(self._query, s)
  File
"/usr/lib/python2.4/site-packages/SQLObject-0.8dev_r1596-py2.4.egg/sqlobject/dbconnection.py",
line 228, in _runWithConnection
    val = meth(conn, *args)
  File
"/usr/lib/python2.4/site-packages/SQLObject-0.8dev_r1596-py2.4.egg/sqlobject/dbconnection.py",
line 311, in _query
    self._executeRetry(conn, conn.cursor(), s)
  File
"/usr/lib/python2.4/site-packages/SQLObject-0.8dev_r1596-py2.4.egg/sqlobject/mysql/mysqlconnection.py",
line 66, in _executeRetry
    return cursor.execute(query)
  File
"/usr/lib/python2.4/site-packages/MySQLdb/cursors.py",
line 137, in execute
    self.errorhandler(self, exc, value)
  File
"/usr/lib/python2.4/site-packages/MySQLdb/connections.py",
line 33, in defaulterrorhandler
    raise errorclass, errorvalue
_mysql_exceptions.OperationalError: (1005, "Can't
create table './test/#sql-1cf0_b9.frm' (errno: 150)")

The problem is because sqlobject creates the Channel
table before the Network table and therefore the query
(despite the order that "tg-admin sql sql" seem to
suggest):

    ALTER TABLE channel ADD CONSTRAINT
channel_network_id_exists FOREIGN KEY (network_id)
REFERENCES network (id);

is executed before the Network table has been created.
I think the problem can be solved by making it so all
constraint statements are executed after the create
table statements are executed.  

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

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