Professional Web Applications Themes

Inconsistent error with create table statement containing foreign key constraint - MySQL

Here is the clause defining a foreign key constraint. CONSTRAINT `contact_id_fk` FOREIGN KEY (`contact_id`) REFERENCES `conts` (`contact_id`) This clause does not end with a comma since it is the last clause in the create table statement. This clause works perfectly in the first table in which it is used, provided the conts table is created first. In the second and third tables where it is used, it generates an error like the following:. ERROR 1005 (HY000) at line 121: Can't create table '.\test\prods.frm' (errno: 121) This isn't a very informative error message. What does it mean? Why would the foreign ...

  1. #1

    Default Inconsistent error with create table statement containing foreign key constraint

    Here is the clause defining a foreign key constraint.

    CONSTRAINT `contact_id_fk` FOREIGN KEY (`contact_id`) REFERENCES
    `conts` (`contact_id`)

    This clause does not end with a comma since it is the last clause in
    the create table statement.

    This clause works perfectly in the first table in which it is used,
    provided the conts table is created first.

    In the second and third tables where it is used, it generates an error
    like the following:.

    ERROR 1005 (HY000) at line 121: Can't create table '.\test\prods.frm'
    (errno: 121)

    This isn't a very informative error message. What does it mean? Why
    would the foreign key constraint clause work fine in the first table
    where it is used and not in the others?

    Thanks.

    Ted

    Ted Guest

  2. #2

    Default Re: Inconsistent error with create table statement containing foreignkey constraint

    Ted wrote:
    > Here is the clause defining a foreign key constraint.
    >
    > CONSTRAINT `contact_id_fk` FOREIGN KEY (`contact_id`) REFERENCES
    > `conts` (`contact_id`)
    >
    > This clause does not end with a comma since it is the last clause in
    > the create table statement.
    >
    > This clause works perfectly in the first table in which it is used,
    > provided the conts table is created first.
    >
    > In the second and third tables where it is used, it generates an error
    > like the following:.
    >
    > ERROR 1005 (HY000) at line 121: Can't create table '.\test\prods.frm'
    > (errno: 121)
    >
    > This isn't a very informative error message. What does it mean? Why
    > would the foreign key constraint clause work fine in the first table
    > where it is used and not in the others?
    >
    > Thanks.
    >
    > Ted
    >
    Maybe it needs to end in a semicolon?

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    [email]jstucklexattglobal.net[/email]
    ==================
    Jerry Stuckle Guest

  3. #3

    Default Re: Inconsistent error with create table statement containing foreign key constraint

    "Ted" <r.ted.byersrogers.com> wrote:
    > Here is the clause defining a foreign key constraint.
    >
    > CONSTRAINT `contact_id_fk` FOREIGN KEY (`contact_id`) REFERENCES
    > `conts` (`contact_id`)
    >
    > This clause works perfectly in the first table in which it is used,
    > provided the conts table is created first.
    >
    > In the second and third tables where it is used, it generates an error
    > like the following:.
    >
    > ERROR 1005 (HY000) at line 121: Can't create table '.\test\prods.frm'
    > (errno: 121)
    ~ $perror 121
    OS error code 121: Remote I/O error
    MySQL error code 121: Duplicate key on write or update

    "Duplicate key" looks promising
    > This isn't a very informative error message. What does it mean? Why
    > would the foreign key constraint clause work fine in the first table
    > where it is used and not in the others?
    I guess you have a duplicate name problem with your constraints.
    InnoDB keeps (invisible) table for constraints and failes to insert
    your contact_id_fk foreign key for the second time. I recommend not
    to specify names for foreign keys at all but have InnoDB pick a name
    by itself.


    XL
    --
    Axel Schwenke, Senior Software Developer, MySQL AB

    Online User Manual: [url]http://dev.mysql.com/doc/refman/5.0/en/[/url]
    MySQL User Forums: [url]http://forums.mysql.com/[/url]
    Axel Schwenke Guest

  4. #4

    Default Re: Inconsistent error with create table statement containing foreignkey constraint

    Axel Schwenke wrote:
    > "Ted" <r.ted.byersrogers.com> wrote:
    > ~ $perror 121
    > OS error code 121: Remote I/O error
    > MySQL error code 121: Duplicate key on write or update
    >
    >> This isn't a very informative error message. What does it mean? Why
    >> would the foreign key constraint clause work fine in the first table
    >> where it is used and not in the others?
    SHOW ENGINE INNODB STATUS;
    also gives a detailed error message in the section "LATEST FOREIGN KEY
    ERROR" (sorry for yelling).
    > I guess you have a duplicate name problem with your constraints.
    > InnoDB keeps (invisible) table for constraints and failes to insert
    > your contact_id_fk foreign key for the second time. I recommend not
    > to specify names for foreign keys at all but have InnoDB pick a name
    > by itself.
    I recommend inventing an own naming schema, different from InnoDB's. The
    first point is, that ting data definition statements over several
    installations will give you predictable results on every site. Thus
    deleting a FK constraint will work like a charm.
    Second InnoDB does not choose names very clever. The constraint names
    are unique over every schema. Before I switched to my naming schema I
    accidentally created a constraint name that matched one in the naming
    schema for another table, but was not used up to this point. Later when
    I created an automatically named constraint in the other table InnoDB
    only looked which name was free *for that table* ignoring the already
    existing name. I spent much time finding that error.

    Greetings
    Kai
    Kai Ruhnau Guest

  5. #5

    Default Re: Inconsistent error with create table statement containing foreign key constraint


    Kai Ruhnau wrote:
    > Axel Schwenke wrote:
    > > "Ted" <r.ted.byersrogers.com> wrote:
    > > ~ $perror 121
    > > OS error code 121: Remote I/O error
    > > MySQL error code 121: Duplicate key on write or update
    > >
    > >> This isn't a very informative error message. What does it mean? Why
    > >> would the foreign key constraint clause work fine in the first table
    > >> where it is used and not in the others?
    >
    > SHOW ENGINE INNODB STATUS;
    > also gives a detailed error message in the section "LATEST FOREIGN KEY
    > ERROR" (sorry for yelling).
    >
    > > I guess you have a duplicate name problem with your constraints.
    > > InnoDB keeps (invisible) table for constraints and failes to insert
    > > your contact_id_fk foreign key for the second time. I recommend not
    > > to specify names for foreign keys at all but have InnoDB pick a name
    > > by itself.
    >
    > I recommend inventing an own naming schema, different from InnoDB's. The
    > first point is, that ting data definition statements over several
    > installations will give you predictable results on every site. Thus
    > deleting a FK constraint will work like a charm.
    > Second InnoDB does not choose names very clever. The constraint names
    > are unique over every schema. Before I switched to my naming schema I
    > accidentally created a constraint name that matched one in the naming
    > schema for another table, but was not used up to this point. Later when
    > I created an automatically named constraint in the other table InnoDB
    > only looked which name was free *for that table* ignoring the already
    > existing name. I spent much time finding that error.
    >
    So foreign key constraints need to be unique across the the whole
    schema, and not just the table? And I had thought that, like field
    names, they applied just to the table in which they were defined, so I
    could have basically the same constraint name in multple tables that
    refer to the same columns in a lookup table. Or am I confused here?

    I was testing these statements individually and in short scripts that I
    could use on any machine, since I have a development machine and will
    eventually deploy both on a test machine and then on a server.

    Thanks guys.

    Ted

    Ted Guest

  6. #6

    Default Re: Inconsistent error with create table statement containing foreign key constraint

    > [snip]
    > I guess you have a duplicate name problem with your constraints.
    > InnoDB keeps (invisible) table for constraints and failes to insert
    > your contact_id_fk foreign key for the second time. I recommend not
    > to specify names for foreign keys at all but have InnoDB pick a name
    > by itself.
    >
    How would you do this if your task is to create a script that can be
    executed on any machine to recreate the database on a new machine? (my
    transition here is from my development machine to a test machine to the
    server where it is to be deployed - we need to recreate the tables and
    relationships, but not copy the test data used for development and
    testing purposes)

    Thanks

    Ted

    Ted Guest

  7. #7

    Default Re: Inconsistent error with create table statement containing foreign key constraint

    "Ted" <r.ted.byersrogers.com> wrote:
    >
    > Kai Ruhnau wrote:
    >>
    >> I recommend inventing an own naming schema, different from InnoDB's.
    >> ... InnoDB does not choose names very clever. The constraint names
    >> are unique over every schema. Before I switched to my naming schema I
    >> accidentally created a constraint name that matched one in the naming
    >> schema for another table, but was not used up to this point. Later when
    >> I created an automatically named constraint in the other table InnoDB
    >> only looked which name was free *for that table* ignoring the already
    >> existing name. I spent much time finding that error.
    >>
    > So foreign key constraints need to be unique across the the whole
    > schema, and not just the table?
    FOREIGN KEY constraints must have unique names across a whole MySQL
    instance. Read here for more details on the foreign key implementation
    in InnoDB:

    [url]http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html[/url]

    The other constraints in MySQL - UNIQUE and PRIMARY keys, are handled
    in the context of the respective tables, so their names must be unique
    in the table only.


    XL
    --
    Axel Schwenke, Senior Software Developer, MySQL AB

    Online User Manual: [url]http://dev.mysql.com/doc/refman/5.0/en/[/url]
    MySQL User Forums: [url]http://forums.mysql.com/[/url]
    Axel Schwenke Guest

  8. #8

    Default Re: Inconsistent error with create table statement containing foreign key constraint

    "Ted" <r.ted.byersrogers.com> wrote:
    >> [snip]
    >> I guess you have a duplicate name problem with your constraints.
    >> InnoDB keeps (invisible) table for constraints and failes to insert
    >> your contact_id_fk foreign key for the second time. I recommend not
    >> to specify names for foreign keys at all but have InnoDB pick a name
    >> by itself.
    >>
    > How would you do this if your task is to create a script that can be
    > executed on any machine to recreate the database on a new machine? (my
    > transition here is from my development machine to a test machine to the
    > server where it is to be deployed - we need to recreate the tables and
    > relationships, but not copy the test data used for development and
    > testing purposes)
    In most conditions one does not need to know the name of a constraint.
    The obvious exception is ALTER TABLE ... DROP FOREIGN KEY, but then you
    can lookup the name before.

    As Kai pointed out, one may wish to use predetermined constraint names,
    i.e. for tion scenarios where the different nodes are not really
    identical copies of the master - otherwise DROP CONSTRAINT could not be
    ted due to different constraint names on different nodes.


    If you intend to recreate a database schema on a new machine - the best
    tool for this job is probably mysqldump. To copy the schema (but not
    data) from database foo on hostA to database bar on hostB, you can use:

    mysqldump -d -h hostA foo | mysql -h hostB bar


    If your question was "how to transport schema changes from development
    to production in a trackable way - without overwriting production from
    scratch every time" - I don't have a good answer yet.

    I used to write down a SQL script containing the respective DML state-
    ments. If this script works against a staging system, it can be used to
    update the production system.

    You might be interested to check [url]http://www.mysqldiff.org/[/url] as well.


    HTH, XL
    --
    Axel Schwenke, Senior Software Developer, MySQL AB

    Online User Manual: [url]http://dev.mysql.com/doc/refman/5.0/en/[/url]
    MySQL User Forums: [url]http://forums.mysql.com/[/url]
    Axel Schwenke Guest

  9. #9

    Default Re: Inconsistent error with create table statement containing foreignkey constraint

    Axel Schwenke wrote:
    > "Ted" <r.ted.byersrogers.com> wrote:
    >> So foreign key constraints need to be unique across the the whole
    >> schema, and not just the table?
    >
    > FOREIGN KEY constraints must have unique names across a whole MySQL
    > instance. Read here for more details on the foreign key implementation
    > in InnoDB:
    >
    > [url]http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html[/url]
    Do you really mean MySQL instance? My interpretation of

    "If the CONSTRAINT symbol clause is given, the symbol value must be
    unique in the database."

    was, that each schema (database) has its own namespace.

    Greetings
    Kai

    P.S. Your signature is missing a ' ' after '--'

    --
    This signature is left as an exercise for the reader.
    Kai Ruhnau Guest

  10. #10

    Default Re: Inconsistent error with create table statement containing foreign key constraint

    Kai Ruhnau <kai.newsgrouptragetaschen.dyndns.org> wrote:
    > Axel Schwenke wrote:
    >> "Ted" <r.ted.byersrogers.com> wrote:
    >>> So foreign key constraints need to be unique across the the whole
    >>> schema, and not just the table?
    >>
    >> FOREIGN KEY constraints must have unique names across a whole MySQL
    >> instance. Read here for more details on the foreign key implementation
    >> in InnoDB:
    >>
    >> [url]http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html[/url]
    >
    > Do you really mean MySQL instance? My interpretation of
    >
    > "If the CONSTRAINT symbol clause is given, the symbol value must be
    > unique in the database."
    >
    > was, that each schema (database) has its own namespace.
    Ooops. You're right. I should not post late in the night :-/

    InnoDB internally adds the database name to the user provided
    constraint name. So names must be unique for each schema only.

    BTW, I just read "Note that InnoDB's FOREIGN KEY system tables
    store constraint names as case-insensitive, with the MySQL
    standard latin1_swedish_ci collation."

    So "Frühling" = "fryling" for InnoDB :-)


    XL
    --
    Axel Schwenke, Senior Software Developer, MySQL AB

    Online User Manual: [url]http://dev.mysql.com/doc/refman/5.0/en/[/url]
    MySQL User Forums: [url]http://forums.mysql.com/[/url]
    Axel Schwenke Guest

  11. #11

    Default Re: Inconsistent error with create table statement containing foreign key constraint

    Axel Schwenke <axel.schwenkegmx.de> wrote:
    >
    > BTW, I just read "Note that InnoDB's FOREIGN KEY system tables
    > store constraint names as case-insensitive, with the MySQL
    > standard latin1_swedish_ci collation."
    >
    > So "Frühling" = "fryling" for InnoDB :-)
    <grr>

    "Frühling" = "fryhling"


    XL
    --
    Axel Schwenke, Senior Software Developer, MySQL AB

    Online User Manual: [url]http://dev.mysql.com/doc/refman/5.0/en/[/url]
    MySQL User Forums: [url]http://forums.mysql.com/[/url]
    Axel Schwenke Guest

  12. #12

    Default Re: Inconsistent error with create table statement containing foreign key constraint

    Thanks Axel,

    Axel Schwenke wrote:
    > "Ted" <r.ted.byersrogers.com> wrote:
    > >> [snip]
    >
    > If your question was "how to transport schema changes from development
    > to production in a trackable way - without overwriting production from
    > scratch every time" - I don't have a good answer yet.
    >
    I'd love to hear about it if you find a good solution. I was told
    today that my scripts for creating the tables and various indeces are
    fine during development leading up to testing and finally deployment,
    but that we need to be able to alter them from development through to
    production in a trackable way and without risking the data in the
    production deployment. Several of these tables will be used to manage
    data feeds, some of which amount to close to ten megabytes a day.
    After a while, I fear changes, especially in indexing, may become
    outrageously expensive.


    BTW, I did not know about "SHOW ENGINE INNODB STATUS;" Kai's
    suggestion proved to be the most important thing I learned here since
    it proved very valuable in learning what MySQL was complaining about.
    I found three kinds of errors leadng to inconsistent behaviour in
    syntactically correct foreign key constraint definitions.

    1) I hadn't realized the foreign ley name had to be unique across the
    database. Providing names I could guarantee would be unique, fixed
    many of my problems.

    2) In some cases, I'd forgotten to create indeces on the fields
    involved in the constraint. Fixing this solved a few more of my
    problems.

    3) In some cases, involving tables I hadn't looked at in a few weeks,
    I'd made the mistake of having different types for the fields that were
    to be related in the foreign key constraint. Fixing this solved the
    rest.

    Thanks one and all for your assistance in this matter.

    Ted

    Ted Guest

Similar Threads

  1. Replies: 1
    Last Post: September 15th, 05:46 PM
  2. Building a foreign key table
    By Cary in forum ASP
    Replies: 2
    Last Post: August 5th, 11:25 PM
  3. Best way to create this constraint?
    By Zig Mandel in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 1st, 12:52 PM
  4. Can Not Access Foreign Key Constraint
    By Rajesh Tiwari in forum ASP.NET General
    Replies: 0
    Last Post: June 30th, 02:36 PM
  5. Replies: 1
    Last Post: December 9th, 03: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