[SQL-CVS] [ sqlobject-Bugs-1663785 ] ADD CONSTRAINT fails

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

Bugs item #1663785, was opened at 2007-02-19 21:49
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=1663785&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: MySQL
Group: SQLObject from repository
Status: Open
Resolution: None
Priority: 5
Private: No
Submitted By: dado1945 (dado1945)
Assigned to: Nobody/Anonymous (nobody)
Summary: ADD CONSTRAINT fails

Initial Comment:
Environment: Windows, MySql 5.0, SQLObject from svn

Description: (1) It seems SQLObject creates tables alphabetically and adds constraints after creation of each table. It might happen that required table is not created yet for constraint. It is interesting that "sql sql" shows that constraints will be added after all tables are created. (2) Another problem is that foreign key and id has different types (INT and INT UNSIGNED). Because of those two problems constrain creation fails.

E.g. this query:
ALTER TABLE blog ADD CONSTRAINT blog_language_id_exists FOREIGN KEY (language_id) REFERENCES language (id)

gives following error:
sqlobject.dberrors.OperationalError: Can't create table '.\tp\#sql-1b8_35.frm' (errno: 150)

(1) This problem might be solved by changing line 590 in file sqlobject\manager\command.py:

tableConstraints = soClass.createTable()

to:

tableConstraints = soClass.createTable(applyConstraints=False)

I guess the same fix is required by line 583.

(2) I think foreign key type must be changed to UNSIGNED INT but I have not found part where to do that. Because of that I just removed UNSIGNED from sqlobject\mysql\mysqlconnection.py line 172. That's not the best fix but the fastest one I have found.

Note: I guess this problem might exist for some other databases as well.

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

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