constraints bug

How-To-Repeat:

Run the following commands:

create table test1
(
Id integer(4) unsigned not null primary key
) type = innoDb;

create table test2
(
Id integer(4) unsigned not null primary key,
Fk integer(4) unsigned not null
) type = innoDb;

create index ITest2 on test2 (Fk);

alter table test2 add constraint constraint_test
Foreign Key (Fk) References test1(Id);

alter table test2 add constraint constraint_test
Foreign Key (Fk) References test1(Id);

alter table test2 add constraint constraint_test
Foreign Key (Fk) References test1(Id);

show table status like 'test2';

At this point you can see three equal constraints. I guess it should
inform an error and not create a constraint that already exists.constraints bug

bye,

sql,query



------------------------------------------------------------ -------------
++ Dyego Souza do Carmo ++ Dep. Desenvolvimento
------------------------------------------------------------ -------------
E S C R I B A I N F O R M A T I C A
------------------------------------------------------------ -------------
The only stupid question is the unasked one (somewhere in Linux's HowTo)
Linux registred user : #230601
--
$ look into "my eyes" Phone : +55 041 296-2311 r.112
look: cannot open my eyes Fax : +55 041 296-6640
------------------------------------------------------------ -------------
Reply: dyego@escriba.com.br



------------------------------------------------------------ ---------
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 bugs-thread13128@lists.mysql.com
To unsubscribe, e-mail

Re: constraints bug

----- Original Message -----
From: "Heikki Tuuri"
To: "Alexander Keremidarski"
Cc: "Sinisa Milivojevic" ;
Sent: Wednesday, November 27, 2002 7:27 PM
Subject: Re: constraints bug


> Dyego, Alex,
>
> ----- Original Message -----
> From: "Alexander Keremidarski"
> To: "Heikki Tuuri"
> Cc: "Sinisa Milivojevic" ;
> Sent: Wednesday, November 27, 2002 5:14 PM
> Subject: Re: constraints bug
>
>
> > Heikki,
> > Can you please comment on this?
> >
> > > Subject: constraints bug
> > > From: Dyego Souza do Carmo
> > > To: bugs@lists.mysql.com
> > > Date: Wed, 27 Nov 2002 11:07:15 -0200
> > >
> > > Run the following commands:
> > >
> > > create table test1
> > > (
> > > Id integer(4) unsigned not null primary key
> > > ) type = innoDb;
> > >
> > > create table test2
> > > (
> > > Id integer(4) unsigned not null primary key,
> > > Fk integer(4) unsigned not null
> > > ) type = innoDb;
> > >
> > > create index ITest2 on test2 (Fk);
> > >
> > > alter table test2 add constraint constraint_test
> > > Foreign Key (Fk) References test1(Id);
> > >
> > > alter table test2 add constraint constraint_test
> > > Foreign Key (Fk) References test1(Id);
> > >
> > > alter table test2 add constraint constraint_test
> > > Foreign Key (Fk) References test1(Id);
> > >
> > > show table status like 'test2';
> > >
> > > At this point you can see three equal constraints. I guess it should
> > > inform an error and not create a constraint that already
> exists.constraints bug
> >
> > mysql> show table status like 'test2'\G
> > *************************** 1. row ***************************
> > Name: test2
> > Type: InnoDB
> > Row_format: Fixed
> > Rows: 0
> > Avg_row_length: 0
> > Data_length: 16384
> > Max_data_length: NULL
> > Index_length: 16384
> > Data_free: 0
> > Auto_increment: NULL
> > Create_time: NULL
> > Update_time: NULL
> > Check_time: NULL
> > Create_options:
> > Comment: InnoDB free: 380928 kB; (Fk) REFER bugs/test1(Id);
(Fk)
> REFER
> > bugs/test1(Id); (Fk) REFER bugs/test1(Id)
>
>
> I would not call this a 'bug' but a missing optimization. InnoDB does not
> check if there already is an identical FOREIGN KEY declaration for the
> tables.
>
>
> How-To-Repeat:
>
> > --
> > For technical support contracts, visit https://order.mysql.com/
> > __ ___ ___ ____ __
> > / |/ /_ __/ __/ __ \/ / Mr. Alexander Keremidarski
>
> > / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
> > /_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria
> > <___/ www.mysql.com M: +359 88 231668
>
> 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
>
>



------------------------------------------------------------ ---------
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 bugs-thread13132@lists.mysql.com
To unsubscribe, e-mail

Re: constraints bug

Hello Dyego,

On Wed, 2002-11-27 at 15:07, Dyego Souza do Carmo wrote:

> alter table test2 add constraint constraint_test
> Foreign Key (Fk) References test1(Id);
>
> alter table test2 add constraint constraint_test
> Foreign Key (Fk) References test1(Id);
>
> alter table test2 add constraint constraint_test
> Foreign Key (Fk) References test1(Id);
>
> show table status like 'test2';
>
> At this point you can see three equal constraints. I guess it should
> inform an error and not create a constraint that already exists.constraints bug


Comment from Heikki Tuury:

> I would not call this a 'bug' but a missing optimization. InnoDB does not
> check if there already is an identical FOREIGN KEY declaration for the
> tables.


------------------------------------------------------------ ---------
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 bugs-thread13139@lists.mysql.com
To unsubscribe, e-mail