Professional Web Applications Themes

Foreign Key Constraints - MySQL

Hi - I'm trying to create foreign key constraints for a few tables in InnoDB tables. Here are the pertinent parts of the create statements: CREATE TABLE a ( id INT NOT NULL AUTO_INCREMENT, .... PRIMARY KEY (id) .... CREATE TABLE b ( id INT NOT NULL AUTO_INCREMENT, .... PRIMARY KEY (id) .... CREATE TABLE connex ( a_id INT NOT NULL, b_id INT NOT NULL, id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (id), FOREIGN KEY (a_id) REFERENCES a(id) ON DELETE CASCADE, FOREIGN KEY (b_id) REFERENCES b(id) ON DELETE CASCADE ) ENGINE = InnoDB; This gives a: #1005 - Can't create ...

  1. #1

    Default Foreign Key Constraints

    Hi - I'm trying to create foreign key constraints for a few tables in
    InnoDB tables. Here are the pertinent parts of the create statements:

    CREATE TABLE a (
    id INT NOT NULL AUTO_INCREMENT,
    ....
    PRIMARY KEY (id)
    ....

    CREATE TABLE b (
    id INT NOT NULL AUTO_INCREMENT,
    ....
    PRIMARY KEY (id)
    ....

    CREATE TABLE connex (
    a_id INT NOT NULL,
    b_id INT NOT NULL,
    id INT NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (id),
    FOREIGN KEY (a_id) REFERENCES a(id) ON DELETE CASCADE,
    FOREIGN KEY (b_id) REFERENCES b(id) ON DELETE CASCADE
    ) ENGINE = InnoDB;

    This gives a:

    #1005 - Can't create table './admin_connex/connex.frm' (errno: 150)

    Which is a malformed foreign key constraint ... Can anyone help me spot
    it? Thanks very much,

    -- whit

    pengypenguin@gmail.com Guest

  2. #2

    Default Re: Foreign Key Constraints

    Depending of your MySQL version, you'll have to create indexes along
    with the foreign keys. The foreign key is then just a constraint - not
    yet an index.

    com wrote: 
    Dikkie Guest

  3. #3

    Default Re: Foreign Key Constraints

    Although I am not certain what an index is, I added one, so the code is
    like this:
    CREATE TABLE a (
    id INT NOT NULL AUTO_INCREMENT,
    ....
    PRIMARY KEY (id)
    ....

    CREATE TABLE b (
    id INT NOT NULL AUTO_INCREMENT,
    ....
    PRIMARY KEY (id)
    ....

    CREATE TABLE connex (
    id INT NOT NULL AUTO_INCREMENT,
    a_id INT NOT NULL,
    b_id INT NOT NULL,
    PRIMARY KEY (id),
    INDEX(a_id),
    FOREIGN KEY (a_id) REFERENCES a(id) ON DELETE CASCADE,
    INDEX(b_id),
    FOREIGN KEY (b_id) REFERENCES b(id) ON DELETE CASCADE
    ) ENGINE = InnoDB;

    Same error.

    Dikkie Dik wrote: [/ref]

    pengypenguin@gmail.com Guest

  4. #4

    Default Re: Foreign Key Constraints

    Got it. I guess there were some funny characters in the create script.
    :P

    -- whit

    com wrote: [/ref][/ref]

    pengypenguin@gmail.com Guest

  5. #5

    Default Re: Foreign Key Constraints

    SHOW ENGINE INNODB STATUS

    If MySQL reports an error number 1005 from a CREATE TABLE statement,
    and the error message refers to errno 150, table creation failed
    because a foreign key constraint was not correctly formed. Similarly,
    if an ALTER TABLE fails and it refers to errno 150, that means a
    foreign key definition would be incorrectly formed for the altered
    table. You can use SHOW ENGINE INNODB STATUS to display a detailed
    explanation of the most recent InnoDB foreign key error in the server.

    ;-)


    com ha escrit: [/ref][/ref]

    joan.vidal@gmail.com Guest

  6. #6

    Default Foreign Key Constraints

    Hello -
    I've created several tables with FKC's, but for some reason, this one
    won't work...

    DROP TABLE IF EXISTS bi_votes;

    CREATE TABLE bi_votes(
    id INT NOT NULL AUTO_INCREMENT,
    bi_id INT NOT NULL,
    f_id INT NOT NULL,
    PRIMARY KEY (id),
    INDEX (bi_id),
    FOREIGN KEY (bi_id) REFERENCES bi(id) ON DELETE CASCADE,
    INDEX (f_id),
    FOREIGN KEY (f_id) REFERENCES f(id) ON DELETE CASCADE
    ) ENGINE = InnoDB;

    I am getting a ERRNO of 150, malformed FKC ... The foreign tables are
    in place. I can't determine the problem. Any thoughts? Thanks in
    advance...

    -- whit

    pengypenguin@gmail.com Guest

  7. #7

    Default Re: Foreign Key Constraints


    com wrote:
     
    I am led to believe, from an earlier thread started by you that:
    "You can use SHOW ENGINE INNODB STATUS to display a detailed
    explanation of the most recent InnoDB foreign key error in the server."

    What does this show in this case?

    Captain Guest

  8. #8

    Default Re: Foreign Key Constraints

    >From me? My goodness. Have I been sleep coding again? I'm afraid I
    don't recall. Unfortunately my access to the server is somewhat
    restricted ...

    #1227 - Access denied; you need the SUPER privilege for this operation

    Rats.

    -- whit

    Captain Paralytic wrote: 
    > I am led to believe, from an earlier thread started by you that:
    > "You can use SHOW ENGINE INNODB STATUS to display a detailed
    > explanation of the most recent InnoDB foreign key error in the server."
    >
    > What does this show in this case?[/ref]

    pengypenguin@gmail.com Guest

  9. #9

    Default Re: Foreign Key Constraints

    Aha -

    Turns out there was a character in there that looked like a space, but
    wasn't. I rewrote the thing from scratch and bango. Thanks yall.

    -- whit

    com wrote: 
    > don't recall. Unfortunately my access to the server is somewhat
    > restricted ...
    >
    > #1227 - Access denied; you need the SUPER privilege for this operation
    >
    > Rats.
    >
    > -- whit
    >
    > Captain Paralytic wrote: 
    > > I am led to believe, from an earlier thread started by you that:
    > > "You can use SHOW ENGINE INNODB STATUS to display a detailed
    > > explanation of the most recent InnoDB foreign key error in the server."
    > >
    > > What does this show in this case?[/ref][/ref]

    pengypenguin@gmail.com Guest

Similar Threads

  1. Replies: 3
    Last Post: March 22nd, 12:41 PM
  2. How are foreign key constraints built?
    By Wes in forum PostgreSQL / PGSQL
    Replies: 8
    Last Post: January 25th, 11:35 PM
  3. How to switch off constraints?
    By Joachim Engel in forum Informix
    Replies: 7
    Last Post: October 13th, 04:36 PM
  4. Replies: 2
    Last Post: September 16th, 03:16 AM
  5. Replies: 2
    Last Post: December 11th, 06:14 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139