Work at SourceForge, help us to make it a better place! We have an immediate need for a Support Technician in our San Francisco or Denver office.

Close

#159 Table creation order for foreign key constraints

closed-fixed
Oleg Broytman
None
5
2006-11-13
2006-02-15
Björn Lindqvist
No

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.

Discussion

  • Oleg Broytman
    Oleg Broytman
    2006-05-23

    • assigned_to: nobody --> phd
     
  • Logged In: YES
    user_id=944205

    Or perhaps I'll paste it inline... There doesn't seem to be
    a way for a mere commenter to attach a file.

    Also note: The turbogears bug suggests that the patch will
    also fix the problem with dropping tables reported in this
    SQLObject Bug:
    http://sourceforge.net/tracker/index.php?func=detail&aid=1495030&group_id=74338&atid=540672

    ---BEGIN TEST CASE---
    from sqlobject import *
    from turbogears.database import PackageHub

    hub = PackageHub('packagedb')
    __connection__ = hub

    class Collection(SQLObject):
    name = StringCol(length=128, notNone=True)

    class Branch(SQLObject):
    collection = ForeignKey('Collection')
    ---END TEST CASE---

     
  • Oleg Broytman
    Oleg Broytman
    2006-11-13

    • status: open --> closed-fixed