Has somebody got an example of Foreign Keys

[Ben Clewett]

I am using 4.0.10-gamma-max-nt, with 100% innodb, and cannot get Foreign Keys to create.

The help files give this example:

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;

My syntax is:

create table if not exists sub (
code varchar(8) primary key,
name varchar(30) not null,
notes text,
sup varchar(8) not null,
index i_sup (sup),
foreign key (sup) references sup(code) on delete set null
) type=innodb;

I get:

MySql Error: Could not execute command [create table if not exists sub ( code varchar(8) primary key, name varchar(30) not null, notes text, sup varchar(8) not null, index i_sup (sup), foreign key (sup) references sup(code) on delete set null ) type=innodb;] on server because: Can't create table '.\rr\sub.frm' (errno: 150)

Which is very annoying, as I can't move on from here...

The error message is really no help. It will have no problem creating the listed file (.\rr\sub.frm) if there is no foreign key in the schema.

I am sure there is not problem with MySQL or everybody would have complained long ago, so it must be me being stupid... Somewhere...

Please, if anybody has any advice, I really need to know :)

Ben.



---------------------------------------------------------------------
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