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

Reply via email to