Stefan,
are you sure you used the script:
CREATE TABLE Projekt(
id SMALLINT UNSIGNED NOT NULL,
PRIMARY KEY (id)) TYPE=InnoDB;
CREATE TABLE Teilnehmer(
id SMALLINT UNSIGNED NOT NULL,
pid SMALLINT UNSIGNED,
FOREIGN KEY (pid) REFERENCES Projekt (id),
PRIMARY KEY (id),
INDEX IDX_Teilnehmer_1 (pid)) TYPE=InnoDB;
SHOW CREATE TABLE Teilnehmer;
?
On Linux I get:
heikki@hundin:~/mysql-4.0.3-beta-pc-linux-gnu-i686/bin> mysql test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.0.3-beta-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> CREATE TABLE Projekt(
-> id SMALLINT UNSIGNED NOT NULL,
-> PRIMARY KEY (id)) TYPE=InnoDB;
Query OK, 0 rows affected (0.22 sec)
mysql>
mysql> CREATE TABLE Teilnehmer(
-> id SMALLINT UNSIGNED NOT NULL,
-> pid SMALLINT UNSIGNED,
-> FOREIGN KEY (pid) REFERENCES Projekt (id),
-> PRIMARY KEY (id),
-> INDEX IDX_Teilnehmer_1 (pid)) TYPE=InnoDB;
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> SHOW CREATE TABLE Teilnehmer;
+------------+--------------------------------------------------------------
----
----------------------------------------------------------------------------
----
----------------------------------------------------------------------------
----
----------------------+
| Table | Create Table
|
+------------+--------------------------------------------------------------
----
----------------------------------------------------------------------------
----
----------------------------------------------------------------------------
----
----------------------+
| Teilnehmer | CREATE TABLE `Teilnehmer` (
`id` smallint(5) unsigned NOT NULL default '0',
`pid` smallint(5) unsigned default NULL,
PRIMARY KEY (`id`),
KEY `IDX_Teilnehmer_1` (`pid`),
FOREIGN KEY (`pid`) REFERENCES `test.Projekt` (`id`)
) TYPE=InnoDB |
+------------+--------------------------------------------------------------
----
----------------------------------------------------------------------------
----
----------------------------------------------------------------------------
----
----------------------+
1 row in set (0.00 sec)
mysql>
Please test again! Do you have the default charset set to german? The
default is latin1 which I use.
Note that
"
The syntax of a foreign key constraint definition in InnoDB:
[CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)
REFERENCES table_name (index_col_name, ...)
[ON DELETE CASCADE | ON DELETE SET NULL | RESTRICT]
Both tables have to be InnoDB type and there must be an index where the
foreign key and the referenced key are listed as the first columns. InnoDB
does not auto-create indexes on foreign keys or referenced keys: you have to
create them explicitly.
Corresponding columns in the foreign key and the referenced key must have
similar internal data types inside InnoDB so that they can be compared
without a type conversion. The size and the signedness of integer types has
to be the same. The length of string types need not be the same.
...
If MySQL gives the error number 1005 from a CREATE TABLE statement, and the
error message string refers to errno 150, then the 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.
"
Best regards,
Heikki Tuuri
Innobase Oy
---
InnoDB - transactions, hot backup, and foreign key support for MySQL
See http://www.innodb.com, download MySQL-Max from http://www.mysql.com
sql query
----- Original Message -----
From: "Stefan Hinz, iConnect (Berlin)" <[EMAIL PROTECTED]>
To: "Heikki Tuuri" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Saturday, December 14, 2002 10:37 PM
Subject: Re: MySQL crashes with foreign key restraints
> Dear Heikki,
>
> thanks for the hint!
>
> > please upgrade to 4.0.5 and test again.
> > 4.0.1 is a very old alpha release, and lots of bugs have been fixed
since.
>
> But please note what I said about 4.0.3:
>
> >> P.S. I tried the same with 4.0.3-max-nt on a Win2K box. Here, the
server
> >> doesn't crash, but the response is "can't create
> >> '.\projekt\teilnehmer.frm'" with error 150 (unknown error).
>
> I had the same problems with 4.0.4 on Win2K.
>
> Regards,
> --
> Stefan Hinz <[EMAIL PROTECTED]>
> CEO / Geschäftsleitung iConnect GmbH <http://iConnect.de>
> Heesestr. 6, 12169 Berlin (Germany)
> Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3
>
>
> ----- Original Message -----
> From: "Heikki Tuuri" <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Sent: Friday, December 13, 2002 9:47 PM
> Subject: Re: MySQL crashes with foreign key restraints
>
>
> > Stefan,
> >
> > please upgrade to 4.0.5 and test again.
> >
> > 4.0.1 is a very old alpha release, and lots of bugs have been fixed
since.
> >
> > Best regards,
> >
> > Heikki Tuuri
> > Innobase Oy
> > ---
> > Order technical MySQL/InnoDB support at https://order.mysql.com/
> > See http://www.innodb.com for the online manual and latest news on
InnoDB
> >
> > sql query
> >
> > ....................
> > Subject: MySQL crashes with foreign key restraints
> > From: Stefan Hinz
> > Date: Fri, 13 Dec 2002 16:17:56 +0100
> >
> >
>
> --------------------------------------------------------------------------
> --
> > ----
> >
> > Dear list,
> >
> > I am using MySQL-max 4.0.1 on Win 98 Second Edition with InnoDB as
> > default table type. Using DeZign, I created the following SQL file with
> > three tables (I left out all those other CHAR and TEXT fields here):
> >
> > CREATE TABLE Projekt(
> > id SMALLINT UNSIGNED NOT NULL,
> > PRIMARY KEY (id));
> >
> > CREATE TABLE Teilnehmer(
> > id SMALLINT UNSIGNED NOT NULL,
> > pid SMALLINT UNSIGNED,
> > FOREIGN KEY (pid) REFERENCES Projekt (id),
> > PRIMARY KEY (id),
> > INDEX IDX_Teilnehmer_1 (pid));
> >
> > CREATE TABLE Bewertung(
> > pid SMALLINT UNSIGNED NOT NULL,
> > #FOREIGN KEY (pid) REFERENCES Projekt (id),
> > INDEX IDX_Bewertung_1 (pid));
> >
> > Importing this in MySQL batch mode makes the MySQL server crash
> > immediately. Everything works fine for table Projekt and Teilnehmer,
> > but the line I commented out here for table Bewertung makes MySQL
> > crash. Table Bewertung doesn't have a primary key, that's all the
> > difference.
> >
> > Starting the server again and logging in, I find the first two tables
> > are fine, but SHOW TABLE STATUS reports only NULL fields for table
> > Bewertung, and InnoDB complains that it has no fields at all.
> >
> > Trying to drop this broken table results in 'table Bewertung doesn't
> > exist'. To be able to delete the table (and the database), I have to
> > delete Bewertung.frm manually. This makes InnoDB complain at server
> > start like this:
> >
> > InnoDB: MySQL database directory from another database?
> > InnoDB: Have you copied the .frm file of the table to the
> > InnoDB: data dictionary though MySQL is trying to drop it.
> > InnoDB: Error: table projekt/bewertung does not exist in the InnoDB
> > internal
> > InnoDB: MySQL database directory from another database?
> > InnoDB: Have you copied the .frm file of the table to the
> > InnoDB: data dictionary though MySQL is trying to drop it.
> > InnoDB: Error: table projekt/bewertung does not exist in the InnoDB
> > internal
> > have moved .frm files to another database?
> >
> > Is there something wrong with my foreign key restraints, or is this a
> > bug in InnoDB / MySQL?
> >
> > P.S. I tried the same with 4.0.3-max-nt on a Win2K box. Here, the server
> > doesn't crash, but the response is "can't create
> > '.\projekt\teilnehmer.frm'" with error 150 (unknown error). Commenting
> > out the FOREIGN KEY lines solves the problem, but then again, I have no
> > f.k.restraints :(
>
>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
