Rafal,
I tested also with the `hotele`, and it worked ok.
Please provide your my.cnf and MySQL version number. And test if you can
repeat the error.
Regards,
Heikki
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.0.8-gamma-standard-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> drop database test;
Query OK, 0 rows affected (0.08 sec)
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
mysql> CREATE TABLE `hotele` (
-> `nip_h` int(10) NOT NULL default '0',
-> `nazwa_h` varchar(100) NOT NULL default '',
-> `miasto_h` varchar(50) NOT NULL default '',
-> `kodpocz_h` int(5) NOT NULL default '0',
-> `adres_h` varchar(50) default NULL,
-> `email_h` varchar(50) default NULL,
-> `kontobank_h` int(32) default NULL,
-> `wyprzedaz_h` varchar(10) default NULL,
-> `standard_h` varchar(20) default NULL,
-> `opis_h` varchar(200) default NULL,
-> 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.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
----- Original Message -----
From: "Heikki Tuuri" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, January 09, 2003 11:04 PM
Subject: Re: Bug in foreign keys
> 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
