#211 ADD CONSTRAINT fails

closed-fixed
MySQL (29)
5
2007-03-19
2007-02-19
No

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.

Discussion

  • Oleg Broytman

    Oleg Broytman - 2007-02-20
    • assigned_to: nobody --> phd
    • status: open --> open-accepted
     
  • Oleg Broytman

    Oleg Broytman - 2007-02-20

    Logged In: YES
    user_id=4799
    Originator: NO

    1. Fixed in the SVN, revision 2329.

    2. In the MySQL Reference Manual there is an example:
    CREATE TABLE parent(id INT NOT NULL,
    PRIMARY KEY (id)
    ) TYPE=INNODB;
    CREATE TABLE child(id INT, parent_id INT,
    INDEX par_ind (parent_id),
    FOREIGN KEY (parent_id) REFERENCES parent(id)
    ON DELETE CASCADE
    ) TYPE=INNODB;
    So it seems a foreign keys have no obligation to be UNSIGNED. Why do you think they *must* be UNSIGNED?

     
  • Anonymous - 2007-02-20

    Logged In: YES
    user_id=1533929
    Originator: YES

    1. Thanks :)

    2. I don't think it must be UNSIGNED. I think foreign key must have the same type as id. Let's take your example and change a little bit. I have added UNSIGNED to parent id:

    CREATE TABLE parent(id INT UNSIGNED NOT NULL,
    PRIMARY KEY (id)
    ) TYPE=INNODB;

    CREATE TABLE child(id INT, parent_id INT,
    INDEX par_ind (parent_id),
    FOREIGN KEY (parent_id) REFERENCES parent(id)
    ON DELETE CASCADE
    ) TYPE=INNODB;

    Now child.parent_id and parent.id has different types (even if both are 32 bits). That's not good behavior and that's happens in SQLObject's MySQL case. parent.id will always be UNSIGNED INT (sqlobject\mysql\mysqlconnection.py line 172) and foreign key will be INT (I don't know where it comes from). ADD CONSTRAIN seems to check if types are equal and fails.

     
  • Oleg Broytman

    Oleg Broytman - 2007-03-19
    • status: open-accepted --> closed-fixed
     

Log in to post a comment.

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

Sign up for the SourceForge newsletter:





No, thanks