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 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.
jstucklex@attglobal.net
==================

"Ted" <r.ted.byers@rogers.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: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/

Axel Schwenke wrote:
> "Ted" <r.ted.byers@rogers.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 replicating 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 wrote:
> Axel Schwenke wrote:
> > "Ted" <r.ted.byers@rogers.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 replicating 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

> [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" <r.ted.byers@rogers.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:

http://dev.mysql.com/doc/refman/5.0/...nstraints.html

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: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/

"Ted" <r.ted.byers@rogers.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 replication scenarios where the different nodes are not really
identical copies of the master - otherwise DROP CONSTRAINT could not be
replicated 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 http://www.mysqldiff.org/ as well.


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

Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/

Axel Schwenke wrote:
> "Ted" <r.ted.byers@rogers.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:
>
> http://dev.mysql.com/doc/refman/5.0/...nstraints.html

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 <kai.newsgroup@tragetaschen.dyndns.org> wrote:
> Axel Schwenke wrote:
>> "Ted" <r.ted.byers@rogers.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:
>>
>> http://dev.mysql.com/doc/refman/5.0/...nstraints.html
>
> 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: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/