Foreign keys

Hi all,

I have this rather strange thing happening:

Server verions:
- version: mysql 4.0.5 beta-max-nt
- using MY CC 0.8.6 alpha (but the same with for example mysqlgui)

How-To-Repeat:
create 3 test-tables:
CREATE TABLE `t1` (
`Beschrijving` varchar(100) NOT NULL default '',
`Test1` smallint(6) NOT NULL default '0',
PRIMARY KEY (`Test1`)
) TYPE=InnoDB

CREATE TABLE `t2` (
`Manny_Fields` varchar(100) NOT NULL default '',
`Test2` smallint(6) NOT NULL default '0',
PRIMARY KEY (`Test2`)
) TYPE=InnoDB


I then issue a following comand:
CREATE TABLE if not exists `t3` (
`Manny_Fields` varchar(100) NOT NULL default '',
`Test3` smallint(6) NOT NULL default '0' References t1(test1) on delete
set null,
PRIMARY KEY (`Test3`)
) TYPE=InnoDB

Then I issue the command: "show create table t3"
What I see is:
CREATE TABLE `t3` (
`Manny_Fields` varchar(100) NOT NULL default '',
`Test3` smallint(6) NOT NULL default '0',
PRIMARY KEY (`Test3`)
) TYPE=InnoDB

What I expect to see:
CREATE TABLE `t3` (
`Manny_Fields` varchar(100) NOT NULL default '',
`Test3` smallint(6) NOT NULL default '0' References t1(test1) on delete
set null,
PRIMARY KEY (`Test3`)
) TYPE=InnoDB


All the stuff regarding "References t1(Test1) on ....." has gone.
Even after flushing tables, databases, stopping and restarting the server.

I know Mysql (Isam and Myisam) doesn't do anything with the references, but
INNODB does.
Shouldn't the "show create table"-syntax return also the "references ..."?

Is this the 'normal' thing to happen


The same strange thing happens with the "grant .... " if it includes the
"MAX_CONNECTION_PER_HOUR". The privileges get granted ok, but after issuing
a "flush privileges" the "show grants ..." does not return the correct grant


Kris Bekkers
www.vzwalert.be


************************************************************ *******************
Hou uw internetverbruik beter onder controle ... surf met Tiscali Complete
.... http://tiscali.complete.be



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

Re: Foreign keys

krisbekkers@tiscali.be writes:
> Hi all,
>
> I have this rather strange thing happening:
>
> Server verions:
> - version: mysql 4.0.5 beta-max-nt
> - using MY CC 0.8.6 alpha (but the same with for example mysqlgui)
>
> How-To-Repeat:
> create 3 test-tables:
> CREATE TABLE `t1` (
> `Beschrijving` varchar(100) NOT NULL default '',
> `Test1` smallint(6) NOT NULL default '0',
> PRIMARY KEY (`Test1`)
> ) TYPE=InnoDB
>
> CREATE TABLE `t2` (
> `Manny_Fields` varchar(100) NOT NULL default '',
> `Test2` smallint(6) NOT NULL default '0',
> PRIMARY KEY (`Test2`)
> ) TYPE=InnoDB
>
>
> I then issue a following comand:
> CREATE TABLE if not exists `t3` (
> `Manny_Fields` varchar(100) NOT NULL default '',
> `Test3` smallint(6) NOT NULL default '0' References t1(test1) on delete
> set null,
> PRIMARY KEY (`Test3`)
> ) TYPE=InnoDB
>

[skip]

> Is this the 'normal' thing to happen
>
>
> The same strange thing happens with the "grant .... " if it includes the
> "MAX_CONNECTION_PER_HOUR". The privileges get granted ok, but after issuing
> a "flush privileges" the "show grants ..." does not return the correct grant
>
>
> Kris Bekkers
> www.vzwalert.be
>

Hi!

The above is actually expected behaviour, although we could add the
error message here.

You can not have in InnoDB same column as a foreign key and as a
primery key.

--
Regards,
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ 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-thread13077@lists.mysql.com
To unsubscribe, e-mail

Re: Foreign keys

Peter,

----- Original Message -----
From: "Peter Zaitsev"
To: "Heikki Tuuri"
Sent: Friday, November 22, 2002 2:09 PM
Subject: Fwd: Re: Foreign keys


> Dear Heikki
>
> Why did you decided to have this limitation (primary key can't be on the
same
> column as foreign key) ?

Sinisa was in error below, a foreign key CAN be on the primary key column.
The error below is that a foreign key cannot be declared in the COLUMN
definition. It always has to be a separate declaration:

"
The syntax of a foreign key constraint definition in InnoDB:

[CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)
REFERENCES table_name (index_col_name, ...)
[ON DELETE CASCADE | ON DELETE SET NULL | RESTRICT]
"

After correcting the syntax I get:


mysql> CREATE TABLE `t1` (
-> `Beschrijving` varchar(100) NOT NULL default '',
-> `Test1` smallint(6) NOT NULL default '0',
-> PRIMARY KEY (`Test1`)
-> ) TYPE=InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> CREATE TABLE `t2` (
-> `Manny_Fields` varchar(100) NOT NULL default '',
-> `Test2` smallint(6) NOT NULL default '0',
-> PRIMARY KEY (`Test2`)
-> ) TYPE=InnoDB;
Query OK, 0 rows affected (0.02 sec)

mysql>
mysql> CREATE TABLE if not exists `t3` (
-> `Manny_Fields` varchar(100) NOT NULL default '',
-> `Test3` smallint(6) NOT NULL default '0',
-> PRIMARY KEY (`Test3`),
-> FOREIGN KEY (Test3) REFERENCES t1(test1) on delete
-> set null
-> ) TYPE=InnoDB;
ERROR 1005: Can't create table './test/t3.frm' (errno: 150)



Another error! This time the reason is that the user declares ON DELETE SET
NULL, but the corresponding column has the restriction NOT NULL. That gives
the errno 150. If I change it to ON DELETE CASCADE, it finally works:



mysql> CREATE TABLE if not exists `t3` (
-> `Manny_Fields` varchar(100) NOT NULL default '',
-> `Test3` smallint(6) NOT NULL default '0',
-> PRIMARY KEY (`Test3`),
-> FOREIGN KEY (Test3) REFERENCES t1(Test1) on delete
-> cascade
-> ) TYPE=InnoDB;
Query OK, 0 rows affected (0.00 sec)

mysql> show create table t3;
+-------+--------------------------------------------------- ----------------
----
------------------------------------------------------------ ----------------
----
------------------------------------------------------------ -------------+
| Table | Create Table

|
+-------+--------------------------------------------------- ----------------
----
------------------------------------------------------------ ----------------
----
------------------------------------------------------------ -------------+
| t3 | CREATE TABLE `t3` (
`Manny_Fields` varchar(100) NOT NULL default '',
`Test3` smallint(6) NOT NULL default '0',
PRIMARY KEY (`Test3`),
FOREIGN KEY (`Test3`) REFERENCES `t1` (`Test1`) ON DELETE CASCADE
) TYPE=InnoDB |
+-------+--------------------------------------------------- ----------------
----
------------------------------------------------------------ ----------------
----
------------------------------------------------------------ -------------+
1 row in set (0.01 sec)

mysql>



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


How-To-Repeat:

> ---------- Forwarded Message ----------
>
> Subject: Re: Foreign keys
> Date: Fri, 22 Nov 2002 14:01:46 +0200
> From: Sinisa Milivojevic
> To: krisbekkers@tiscali.be
> Cc: bugs@lists.mysql.com
>
> krisbekkers@tiscali.be writes:
> > Hi all,
> >
> > I have this rather strange thing happening:
> >
> > Server verions:
> > - version: mysql 4.0.5 beta-max-nt
> > - using MY CC 0.8.6 alpha (but the same with for example mysqlgui)
> >
> > How-To-Repeat:
> > create 3 test-tables:
> > CREATE TABLE `t1` (
> > `Beschrijving` varchar(100) NOT NULL default '',
> > `Test1` smallint(6) NOT NULL default '0',
> > PRIMARY KEY (`Test1`)
> > ) TYPE=InnoDB
> >
> > CREATE TABLE `t2` (
> > `Manny_Fields` varchar(100) NOT NULL default '',
> > `Test2` smallint(6) NOT NULL default '0',
> > PRIMARY KEY (`Test2`)
> > ) TYPE=InnoDB
> >
> >
> > I then issue a following comand:
> > CREATE TABLE if not exists `t3` (
> > `Manny_Fields` varchar(100) NOT NULL default '',
> > `Test3` smallint(6) NOT NULL default '0' References t1(test1) on
delete
> > set null,
> > PRIMARY KEY (`Test3`)
> > ) TYPE=InnoDB
> >
>
> [skip]
>
> > Is this the 'normal' thing to happen
> >
> >
> > The same strange thing happens with the "grant .... " if it includes the
> > "MAX_CONNECTION_PER_HOUR". The privileges get granted ok, but after
issuing
> > a "flush privileges" the "show grants ..." does not return the correct
grant
> >
> >
> > Kris Bekkers
> > www.vzwalert.be
> >
>
> Hi!
>
> The above is actually expected behaviour, although we could add the
> error message here.
>
> You can not have in InnoDB same column as a foreign key and as a
> primery key.
>
> --
> Regards,
> __ ___ ___ ____ __
> / |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
> /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
> <___/ 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-thread13077@lists.mysql.com
> To unsubscribe, e-mail
>
>
>
> -------------------------------------------------------
>
> --
> __ ___ ___ ____ __
> / |/ /_ __/ __/ __ \/ / Mr. Peter Zaitsev
> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
> /_/ /_/\_, /___/\___\_\___/ Moscow, Russia
> <___/ www.mysql.com M: +7 095 725 4955
>



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