InnoDB FOREIGN KEY error!

JdS · #1 16-May-2003, 15:31

I was trying my hand at InnoDB tables and am having problems with FOREIGN KEYS!

PHP Code:


<?php
$sqls[] = 'CREATE TABLE templatesets (
  tplset_code VARCHAR(4) NOT NULL PRIMARY KEY,
  tplset_name VARCHAR(30) NOT NULL
) TYPE=InnoDB';

$sqls[] = 'CREATE TABLE templatex (
  tpl_id SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  tpl_name VARCHAR(100) NOT NULL,
  tpl_html TEXT NOT NULL,
  set_code VARCHAR(4) NOT NULL,
  KEY tpl_name( tpl_name ),
  KEY set_code( set_code ),
  FOREIGN KEY( set_code ) REFERENCES templatesets( tplset_code ) ON DELETE CASCADE
) TYPE=InnoDB';
?>

I am getting the error no. 1005 i.e. errno 150, that means a foreign key definition would be incorrectly formed.

What could be wrong?

Allowee · #2 16-May-2003, 16:18

you forgot to set something in the table 'templatesets'

quote from MySQL.com

Quote:

Both tables have to be InnoDB type and there must be an index where the foreign key and the referenced key are listed as the FIRST columns. InnoDB does not auto-create indexes on foreign keys or referenced keys: you have to create them explicitly.

JdS · #3 16-May-2003, 16:29

Hello Allowee,

Thank you for the reply but I don't think I got what you were trying to explain.

A sample correction would be nice

Allowee · #4 17-May-2003, 03:01

PHP Code:


CREATE TABLE parent(id INT NOT NULL, PRIMARY KEY (id)) TYPE=INNODB;
CREATE TABLE child(id INT,
 parent_id INT,
 INDEX par_ind (parent_id),
FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE SET NULL
) TYPE=INNODB;

I got this from MySQL.com

so your code will be something like.

PHP Code:


<?php
$sqls[] = 'CREATE TABLE templatesets (
  tplset_code VARCHAR(4) NOT NULL PRIMARY KEY,
  tplset_name VARCHAR(30) NOT NULL
) TYPE=InnoDB';

$sqls[] = 'CREATE TABLE templatex (
  tpl_id SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  tpl_name VARCHAR(100) NOT NULL,
  tpl_html TEXT NOT NULL,
  set_code VARCHAR(4) NOT NULL,
  KEY tpl_name( tpl_name ),
  KEY set_code( set_code ),
  [b]INDEX tpls_code ( tplset_code ),[/b]
  FOREIGN KEY( set_code ) REFERENCES templatesets( tplset_code ) ON DELETE CASCADE
) TYPE=InnoDB';
?>

I'm not sure if this will work.....

at least you forgot an INDEX.
thats why i put in the quote from MySQL.com

JdS · #5 17-May-2003, 04:45

KEY is a synonym for INDEX.

Yeah, I didn't know that till about a month or so ago, so it's common to be NOT aware of this little detail.

Here's some odd news:

Today, just before getting online, I started my MySQL server and absent-mindedly brought this very page (containing the sql commands above) up and it WORKED!

SO, either my MySQL server last night was screwy or there is a bug with phpmyadmin (since my first attempt trying to create the tables, was through phpmyadmin). I remember I was RELOADING MySQL all the time last night, after each change - yet nothing!

The one thing I didn't do was to STOP and START MySQL. Perhaps that would have solved the problem immediately... I will try to see if I can re-create the problem so I can know for sure what caused it.

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Rate This Thread
Rate This Thread:

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Parse error: parse error, expecting `T_STRING' or `T_VARIABLE' or `T_NUM_STRING' in	ukrspp21	MySQL / PHP Forum	31	04-Jul-2006 21:41
Implicit Declaration error	mike3340	CPP / C++ Forum	1	16-Dec-2003 08:12
error during program	rjd72285	CPP / C++ Forum	0	11-Nov-2003 18:49
[script] E-mail webmaster error page	BobbyDouglas	PHP Code Library	0	19-Aug-2003 20:10
CD burner, focus or tracking error	ShingoDrrazz	Computer Hardware Forum	1	09-Aug-2003 16:26

InnoDB FOREIGN KEY error!

Re: InnoDB FOREIGN KEY error!

What is the difference between KEY and INDEX in MySQL?