Thank You!!
> Rafal, > > the test case below is different from the one you posted yesterday. > > Below you are trying to add a constraint > > " > foreign key(nip_h,nrpok_p) references pokoje(nip_h,nrpok_p) on delete set > null > " > > though you have declared > > " > nip_h int(10) not null > " > > in 'rezerwacje'. InnoDB does not accept the constraint because 'on delete > set null' is not sensible when you declare the column not null. > > I have now added a note to InnoDB manuals about this. > > 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 > > ............................. > Subject: Re: Bug in foreign keys > From: Rafal Jank > Date: Fri, 10 Jan 2003 11:10:35 +0100 > > > ---------------------------------------------------------------------------- > ---- > > On Thu, 09 Jan 2003 23:41:46 +0200 > Heikki Tuuri <[EMAIL PROTECTED]> wrote: > > > 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 > > > MySQL version 4.0.8, my my.cnf is as follows: > > # Example mysql config file for medium systems. > # > # This is for a system with little memory (32M - 64M) where MySQL plays > # a important part and systems up to 128M very MySQL is used together with > # other programs (like a web server) > # > # You can copy this file to > # /etc/mf.cnf to set global options, > # mysql-data-dir/my.cnf to set server-specific options (in this > # installation this directory is /var/lib/mysql) or > # ~/.my.cnf to set user-specific options. > # > # One can in this file use all long options that the program supports. > # If you want to know which options a program support, run the program > # with --help option. > > # The following options will be passed to all MySQL clients > [client] > #password = your_password > port = 3306 > socket = /var/lib/mysql/mysql.sock > > # Here follows entries for some specific programs > > # The MySQL server > [mysqld] > port = 3306 > socket = /var/lib/mysql/mysql.sock > skip-locking > set-variable = key_buffer=16M > set-variable = max_allowed_packet=1M > set-variable = table_cache=64 > set-variable = sort_buffer=512K > set-variable = net_buffer_length=8K > set-variable = myisam_sort_buffer_size=8M > #set-variable = max_user_connections =2 > log-bin > server-id = 1 > default-character-set = latin2 > > # Point the following paths to different dedicated disks > #tmpdir = /tmp/ > #log-update = /path-to-dedicated-directory/hostname > > # Uncomment the following if you are using BDB tables > #set-variable = bdb_cache_size=4M > #set-variable = bdb_max_lock=10000 > > # Uncomment the following if you are using Innobase tables > innodb_data_file_path = ibdata1:25M;ibdata2:37M;ibdata3:100M:autoextend > innodb_data_home_dir = /var/lib/mysql/ > innodb_log_group_home_dir = /var/lib/mysql/ > innodb_log_arch_dir = /var/lib/mysql/ > set-variable = innodb_mirrored_log_groups=1 > set-variable = innodb_log_files_in_group=3 > set-variable = innodb_log_file_size=5M > set-variable = innodb_log_buffer_size=8M > innodb_flush_log_at_trx_commit=1 > innodb_log_archive=0 > set-variable = innodb_buffer_pool_size=16M > set-variable = innodb_additional_mem_pool_size=2M > set-variable = innodb_file_io_threads=4 > set-variable = innodb_lock_wait_timeout=50 > > [mysqldump] > quick > set-variable = max_allowed_packet=16M > > [mysql] > no-auto-rehash > # Remove the next comment character if you are not familiar with SQL > #safe-updates > > and the test case: > > mysql> drop table pokoje,rezerwacje,hotele; > Query OK, 0 rows affected (1.13 sec) > > mysql> create table hotele ( > -> nip_h int(10) not null, > -> nazwa_h varchar(100) not null, > -> miasto_h varchar(50) not null, > -> kodpocz_h int(5) not null, > -> adres_h varchar(50), > -> email_h varchar(50), > -> kontobank_h int(32), > -> wyprzedaz_h varchar(10), > -> standard_h varchar(20), > -> opis_h varchar(200), > -> primary key (nip_h) > -> ) > -> type=innodb; > Query OK, 0 rows affected (0.17 sec) > > mysql> create table pokoje ( > -> nrpok_p char(10) not null, > -> nip_h int(10) not null, > -> lozka_p char(2), > -> tv_p char(1), > -> lazienka_p char(1), > -> cena_p int(10), > -> zaliczka_p int(10), > -> index nip_h_index (nip_h,nrpok_p), > -> foreign key (nip_h) references hotele (nip_h) on delete cascade, > -> primary key (nrpok_p, nip_h) > -> ) > -> type=innodb; > Query OK, 0 rows affected (0.21 sec) > > mysql> create table rezerwacje ( > -> id_r int(10) not null, > -> pesel_k int(11), > -> nip_h int(10) not null, > -> nrpok_p char(10) not null, > -> data_r date, > -> od_r date, > -> do_r date, > -> cena_r int(10), > -> zaliczka_r int(5), > -> zaplac_r char(1), > -> wplaczal_r char(1), > -> index nip_h_index (nip_h,nrpok_p), > -> foreign key(nip_h,nrpok_p) references pokoje(nip_h,nrpok_p) on > delete > se > t null, > -> index pesel_k_index (pesel_k), > -> primary key(id_r)) type=innodb; > ERROR 1005: Can't create table './test/rezerwacje.frm' (errno: 150) > > > > > > > > --------------------------------------------------------------------- > 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 > -- _/ _/ _/_/_/ ----- 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 *==---------- --------------------------------------------------------------------- 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
