Rafal,
----- Original Message -----
From: "Rafal Jank" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.mysql
Sent: Thursday, January 09, 2003 6:18 PM
Subject: Bug in foreign keys
> Hi!
> I have two tables:
> CREATE TABLE `pokoje` (
> `nrpok_p` char(10) NOT NULL default '',
> `nip_h` int(10) NOT NULL default '0',
> `lozka_p` char(2) default NULL,
> `tv_p` char(1) default NULL,
> `lazienka_p` char(1) default NULL,
> `cena_p` int(10) default NULL,
> `zaliczka_p` int(10) default NULL,
> PRIMARY KEY (`nrpok_p`,`nip_h`),
> KEY `nip_h_index` (`nip_h`,`nrpok_p`),
> FOREIGN KEY (`nip_h`) REFERENCES `hotele` (`nip_h`) ON DELETE CASCADE
> ) TYPE=InnoDB;
>
> CREATE TABLE `rezerwacje` (
> `id_r` int(10) NOT NULL default '0',
> `pesel_k` int(11) default NULL,
> `nip_h` int(10) NOT NULL default '0',
> `nrpok_p` char(10) NOT NULL default '',
> `data_r` date default NULL,
> `od_r` date default NULL,
> `do_r` date default NULL,
> `cena_r` int(10) default NULL,
> `zaliczka_r` int(5) default NULL,
> `zaplac_r` char(1) default NULL,
> `wplaczal_r` char(1) default NULL,
> PRIMARY KEY (`id_r`),
> KEY `nip_h_index` (`nip_h`,`nrpok_p`)
> ) TYPE=InnoDB;
>
> Now, when I try:
> alter table rezerwacje add foreign key (nip_h,nrpok_p) references
> pokoje(nip_h,nrpok_p);
> I get:
> ERROR 1005: Can't create table './test/#sql-932_4.frm' (errno: 150)
>
> Why?
what is your MySQL version? What is the OS? What is the
default-character-set in my.cnf? What does SHOW CREATE TABLE say for every
table involved, also `hotel`? Can you write a script which repeats the
error?
I tested a slightly more complex script in the latest MySQL-4.0 source tree,
with the latin1_de charset and it ran without errors:
CREATE TABLE `hotele` (
`nip_h` int(10) NOT NULL default '0',
`abbaguu` int(10),
PRIMARY KEY (`nip_h`)
) TYPE = InnoDB;
CREATE TABLE `pokoje` (
`nrpok_p` char(10) NOT NULL default '',
`nip_h` int(10) NOT NULL default '0',
`lozka_p` char(2) default NULL,
`tv_p` char(1) default NULL,
`lazienka_p` char(1) default NULL,
`cena_p` int(10) default NULL,
`zaliczka_p` int(10) default NULL,
PRIMARY KEY (`nrpok_p`,`nip_h`),
FOREIGN KEY (`nip_h`) REFERENCES `hotele` (`nip_h`) ON DELETE CASCADE,
KEY `nip_h_index` (`nip_h`,`nrpok_p`),
) TYPE=InnoDB;
CREATE TABLE `rezerwacje` (
`id_r` int(10) NOT NULL default '0',
`pesel_k` int(11) default NULL,
`nip_h` int(10) NOT NULL default '0',
`nrpok_p` char(10) NOT NULL default '',
`data_r` date default NULL,
`od_r` date default NULL,
`do_r` date default NULL,
`cena_r` int(10) default NULL,
`zaliczka_r` int(5) default NULL,
`zaplac_r` char(1) default NULL,
`wplaczal_r` char(1) default NULL,
PRIMARY KEY (`id_r`),
KEY `nip_h_index` (`nip_h`,`nrpok_p`)
) TYPE=InnoDB;
CREATE TABLE `kukkuu` (
`id_r` int(10) NOT NULL default '0',
PRIMARY KEY (`id_r`),
FOREIGN KEY (`id_r`) REFERENCES `rezerwacje` (`id_r`)
) TYPE=InnoDB;
alter table rezerwacje add foreign key (nip_h,nrpok_p) references
pokoje(nip_h,nrpok_p);
mysql> CREATE TABLE `hotele` (
-> `nip_h` int(10) NOT NULL default '0',
-> `abbaguu` int(10),
-> PRIMARY KEY (`nip_h`)
-> ) TYPE = InnoDB;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> CREATE TABLE `pokoje` (
-> `nrpok_p` char(10) NOT NULL default '',
-> `nip_h` int(10) NOT NULL default '0',
-> `lozka_p` char(2) default NULL,
-> `tv_p` char(1) default NULL,
-> `lazienka_p` char(1) default NULL,
-> `cena_p` int(10) default NULL,
-> `zaliczka_p` int(10) default NULL,
-> PRIMARY KEY (`nrpok_p`,`nip_h`),
-> FOREIGN KEY (`nip_h`) REFERENCES `hotele` (`nip_h`) ON DELETE
CASCADE,
-> KEY `nip_h_index` (`nip_h`,`nrpok_p`),
-> ) TYPE=InnoDB;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> CREATE TABLE `rezerwacje` (
-> `id_r` int(10) NOT NULL default '0',
-> `pesel_k` int(11) default NULL,
-> `nip_h` int(10) NOT NULL default '0',
-> `nrpok_p` char(10) NOT NULL default '',
-> `data_r` date default NULL,
-> `od_r` date default NULL,
-> `do_r` date default NULL,
-> `cena_r` int(10) default NULL,
-> `zaliczka_r` int(5) default NULL,
-> `zaplac_r` char(1) default NULL,
-> `wplaczal_r` char(1) default NULL,
-> PRIMARY KEY (`id_r`),
-> KEY `nip_h_index` (`nip_h`,`nrpok_p`)
-> ) TYPE=InnoDB;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> CREATE TABLE `kukkuu` (
-> `id_r` int(10) NOT NULL default '0',
-> PRIMARY KEY (`id_r`),
-> FOREIGN KEY (`id_r`) REFERENCES `rezerwacje` (`id_r`)
-> ) TYPE=InnoDB;
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> alter table rezerwacje add foreign key (nip_h,nrpok_p) references
-> pokoje(nip_h,nrpok_p);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
> --
> _/ _/ _/_/_/ ----- Rafał Jank [EMAIL PROTECTED] -----
> _/ _/ _/ _/ _/ Wirtualna Polska SA http://www.wp.pl
> _/_/_/_/ _/_/_/ ul. Traugutta 115c, 80-237 Gdansk, tel/fax.
(58)5215625
> _/ _/ _/ --------==* http://szukaj.wp.pl *==----------
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
---------------------------------------------------------------------
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
