Alter table problem

[Ferdek]

Hello everybody !

I just give up. Can somebody tell me, what I do wrong ?
Here is script:

CREATE DATABASE servcontrol;

USE servcontrol;

CREATE TABLE czesc (
  id_czesci CHAR(100) NOT NULL,
  id_hurtowni TINYINT UNSIGNED NOT NULL,
  id_nazwy INT UNSIGNED NOT NULL,
  dokument SMALLINT UNSIGNED NOT NULL,
  data_zakupu DATE NULL DEFAULT '-00-00',
  okres_gw ENUM('brak','3 m-ce','6 m-cy','12 m-cy','24 m-ce','36 m-cy','60
m-cy') NOT NULL,
  okres_gwk ENUM('brak','3 m-ce','6 m-cy','12 m-cy','24 m-ce','36 m-cy','60
m-cy') NOT NULL
) TYPE = INNODB;

CREATE TABLE nazwa (
  id_nazwy INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  nazwa CHAR(100) NOT NULL,
  kod CHAR(20) NOT NULL
) TYPE = INNODB;

CREATE TABLE hurtownia (
  id_hurtowni TINYINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  nazwa CHAR(200) NOT NULL,
  kod CHAR(20) NOT NULL,
  adres CHAR(250) NOT NULL,
  telefon CHAR(50),
  email CHAR(100),
  www CHAR(100),
  dokument SMALLINT UNSIGNED NOT NULL
) TYPE = INNODB;

CREATE TABLE klient (
  id_klienta INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  imie CHAR(30),
  nazwisko CHAR(50),
  adres CHAR(250) NOT NULL,
  telefon CHAR(50),
  email CHAR(100),
  uwagi TEXT,
  firma CHAR(200),
  kod CHAR(20)
) TYPE = INNODB;

CREATE TABLE gwarancja (
  id_gwarancji INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  id_klienta INT UNSIGNED,
  data DATE NOT NULL
) TYPE = INNODB;

CREATE TABLE gwarancja_detal (
  id_gwarancji INT UNSIGNED NOT NULL,
  id_czesci CHAR(100) NOT NULL
) TYPE = INNODB;

CREATE TABLE problemy (
  id_czesci CHAR(100) NOT NULL,
  data DATE NOT NULL,
  opis TEXT NOT NULL
) TYPE = INNODB;

CREATE TABLE reklamacja (
  id_zdarzenia INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  id_czesci CHAR(100) NOT NULL,
  id_klienta INT UNSIGNED NOT NULL,
  data_rekl DATE NOT NULL,
  opis_uszk TEXT NOT NULL,
  uwagi TEXT,
  osprzet TEXT,
  data_odd DATE,
  data_nap DATE,
  opis_nap TEXT,
  id_zastepczy char(100)
) TYPE = INNODB;

CREATE TABLE wymienione (
  id_czesci_s CHAR(100) NOT NULL,
  id_czesci_n CHAR(100) NOT NULL,
  data DATE NOT NULL,
  opis TEXT NOT NULL
) TYPE = INNODB;

CREATE TABLE wyslane (
  id_wyslania INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  id_czesci CHAR(100) NOT NULL,
  id_hurtowni TINYINT UNSIGNED NOT NULL,
  data_wys DATE NOT NULL,
  opis_uszk TEXT NOT NULL,
  uwagi TEXT,
  data_odd DATE,
  opis_nap TEXT
) TYPE = INNODB;

CREATE TABLE pozyczka (
  id_osoby INT UNSIGNED NOT NULL,
  id_czesci CHAR(100) NOT NULL,
  data DATE NOT NULL
) TYPE = INNODB;

CREATE TABLE pracownicy (
  id_osoby INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  imie CHAR(30),
  nazwisko CHAR(50)
) TYPE = INNODB;

ALTER TABLE czesc ADD PRIMARY KEY(id_czesci);

ALTER TABLE nazwa ADD UNIQUE(nazwa);
ALTER TABLE nazwa ADD UNIQUE(kod);
ALTER TABLE hurtownia ADD UNIQUE(nazwa);
ALTER TABLE hurtownia ADD UNIQUE(kod);

ALTER TABLE czesc ADD INDEX(id_hurtowni);
ALTER TABLE czesc ADD INDEX(id_nazwy);
ALTER TABLE gwarancja ADD INDEX(id_klienta);
ALTER TABLE gwarancja_detal ADD INDEX(id_gwarancji);
ALTER TABLE gwarancja_detal ADD INDEX(id_czesci);
ALTER TABLE problemy ADD INDEX(id_czesci);
ALTER TABLE reklamacja ADD INDEX(id_czesci);
ALTER TABLE reklamacja ADD INDEX(id_klienta);
ALTER TABLE reklamacja ADD INDEX(id_zastepczy);
ALTER TABLE wymienione ADD INDEX(id_czesci_s);
ALTER TABLE wymienione ADD INDEX(id_czesci_n);
ALTER TABLE wyslane ADD INDEX(id_czesci);
ALTER TABLE wyslane ADD INDEX(id_hurtowni);
ALTER TABLE pozyczka ADD INDEX(id_osoby);
ALTER TABLE pozyczka ADD INDEX(id_czesci);

ALTER TABLE czesc ADD CONSTRAINT FOREIGN KEY(id_hurtowni) REFERENCES
hurtownia(id_hurtowni) ON DELETE CASCADE;
ALTER TABLE czesc ADD CONSTRAINT FOREIGN KEY(id_nazwy) REFERENCES
nazwa(id_nazwy) ON DELETE CASCADE;
ALTER TABLE gwarancja ADD CONSTRAINT FOREIGN KEY(id_klienta) REFERENCES
klient(id_klienta) ON DELETE SET NULL;
ALTER TABLE gwarancja_detal ADD CONSTRAINT FOREIGN KEY(id_gwarancji)
REFERENCES gwarancja(id_gwarancji) ON DELETE CASCADE;
ALTER TABLE gwarancja_detal ADD CONSTRAINT FOREIGN KEY(id_czesci) REFERENCES
czesc(id_czesci) ON DELETE CASCADE;
/* HERE */ ALTER TABLE reklamacja ADD CONSTRAINT FOREIGN KEY(id_czesci)
REFERENCES czesc(id_czesci) ON DELETE CASCADE;
/* HERE */ ALTER TABLE reklamacja ADD CONSTRAINT FOREIGN KEY(id_zastepczy)
REFERENCES czesc(id_czesci) ON DELETE SET NULL;
ALTER TABLE reklamacja ADD CONSTRAINT FOREIGN KEY(id_klienta) REFERENCES
klient(id_klienta) ON DELETE CASCADE;
/* HERE */ ALTER TABLE problemy ADD CONSTRAINT FOREIGN KEY(id_czesci)
REFERENCES czesc(id_czesci) ON DELETE CASCADE;
/* HERE */ ALTER TABLE wymienione ADD CONSTRAINT FOREIGN KEY(id_czesci_s)
REFERENCES czesc(id_czesci) ON DELETE CASCADE;
/* HERE */ ALTER TABLE wymienione ADD CONSTRAINT FOREIGN KEY(id_czesci_n)
REFERENCES czesc(id_czesci) ON DELETE CASCADE;
/* HERE */ ALTER TABLE wyslane ADD CONSTRAINT FOREIGN KEY(id_czesci)
REFERENCES czesc(id_czesci) ON DELETE CASCADE;
ALTER TABLE wyslane ADD CONSTRAINT FOREIGN 

Re: Alter table problem

[Heikki Tuuri]

Ferdek,

what character set you have specified in my.cnf? I tested your script with
latin1 (the default) and 3.23.52, and it seemed to work without errors.

For example, for the table reklamacja you see below it created the 3 foreign
key constraints.

Can you provide a simple repeatable test case (and my.cnf) where it fails?

Regards,

Heikki
Innobase Oy


..
mysql show create table reklamacja;
++--





















+
| Table  | Create Table










|
++--





















+
| reklamacja | CREATE TABLE `reklamacja` (
  `id_zdarzenia` int(10) unsigned NOT NULL auto_increment,
  `id_czesci` varchar(100) NOT NULL default '',
  `id_klienta` int(10) unsigned NOT NULL default '0',
  `data_rekl` date NOT NULL default '-00-00',
  `opis_uszk` text NOT NULL,
  `uwagi` text,
  `osprzet` text,
  `data_odd` date default NULL,
  `data_nap` date default NULL,
  `opis_nap` text,
  `id_zastepczy` varchar(100) default NULL,
  PRIMARY KEY  (`id_zdarzenia`),
  KEY `id_czesci` (`id_czesci`),
  KEY `id_klienta` (`id_klienta`),
  KEY `id_zastepczy` (`id_zastepczy`),
  FOREIGN KEY (`id_klienta`) REFERENCES `servcontrol.klient` (`id_klienta`)
ON D
ELETE CASCADE,
  FOREIGN KEY (`id_czesci`) REFERENCES `servcontrol.czesc` (`id_czesci`) ON
DELE
TE CASCADE,
  FOREIGN KEY (`id_zastepczy`) REFERENCES `servcontrol.czesc` (`id_czesci`)
ON D
ELETE SET NULL
) TYPE=InnoDB |
++--





















+
1 row in set (0.00 sec)



-
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

Re: Alter table problem

[Heikki Tuuri]

Ferdek,

I found the bug. If one specifies a non-latin1 character set in my.cnf, then
the internal data type of a CHAR column is DATA_MYSQL inside InnoDB. In that
case the foreign key check did not accept columns of different length.

I have now fixed this to 3.23.52.

But a note on programming style: usually it is logical that the column in
the referenced table should have the exact same data type as the column in
the referencing table.

Thank you for discovering this bug!

Heikki
Innobase Oy

- Original Message -
From: Ferdek [EMAIL PROTECTED]
To: Heikki Tuuri [EMAIL PROTECTED]
Sent: Wednesday, June 19, 2002 12:37 AM
Subject: Re: Alter table problem


  Ferdek,
 
  what character set you have specified in my.cnf? I tested your script
with
  latin1 (the default) and 3.23.52, and it seemed to work without errors.
 
  For example, for the table reklamacja you see below it created the 3
 foreign
  key constraints.
 
  Can you provide a simple repeatable test case (and my.cnf) where it
fails?
 
  Regards,
 
  Heikki
  Innobase Oy

 Hello.

 It's amazing, that it worked without any errors. I use character set
 win1250.
 But I managed problem - I changed type of column id_czesci in the table
 czesc from char to varchar and others columns too. This is a copy of
 manual:

 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.

 So, I had tables with columns (one or more) of type text and char. MySQL
 automaticly change the type char to varchar in this case. And id_czesci
 from table czesc was char. And IMHO this was a problem (comparing
 char with varchar).

 This is a copy of my my.cnf file :

 [mysqld]
 default-character-set=win1250
 basedir=C:/mysql
 innodb_data_home_dir = c:\ibdata
 innodb_data_file_path = ibdata1:50M:autoextend:max:200M
 set-variable = innodb_buffer_pool_size=50M
 set-variable = innodb_additional_mem_pool_size=10M
 innodb_log_group_home_dir=c:\iblogs
 innodb_log_arch_dir = c:\iblogs
 innodb_log_archive=0
 set-variable = innodb_log_files_in_group=3
 set-variable = innodb_log_file_size=10M
 set-variable = innodb_log_buffer_size=8M
 innodb_flush_log_at_trx_commit=1
 set-variable = innodb_file_io_threads=10
 set-variable = innodb_lock_wait_timeout=50
 datadir=C:/mysql/data
 [WinMySQLadmin]
 Server=C:/mysql/bin/mysqld-max-nt.exe
 user=user
 password=password

 Anyway, great thanks !
 If you have any other ideas, why it didn't work,
 I'm looking hearing from you.

 Best regards
 Adrian Sosialuk

 P.S. Sorry for my English.





-
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