create table error

[Trilogen]

I can't figure this out.

I'm getting an error:

[root@localhost:3306] ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near ''mid'),

When trying to create this table:

CREATE TABLE msgbox (
mid int(20) NOT NULL auto_increment,
uid int(11) NOT NULL default '0',
to varchar(30) NOT NULL default '',
from varchar(30) NOT NULL default '',
subject varchar(255) NOT NULL default '',
message text NOT NULL,
time timestamp NOT NULL,
PRIMARY KEY ('mid'),
FOREIGN KEY ('uid') REFERENCES user('uid') ON UPDATE CASCADE ON DELETE CASCADE
) TYPE=InnoDB;

Appreciate any help given.
Thanks.

[drew010]

CREATE TABLE msgbox (
mid int(20) NOT NULL auto_increment,
uid int(11) NOT NULL default '0',
to varchar(30) NOT NULL default '',
from varchar(30) NOT NULL default '',
subject varchar(255) NOT NULL default '',
message text NOT NULL,
time timestamp NOT NULL,
PRIMARY KEY (mid),
FOREIGN KEY (uid) REFERENCES user(uid) ON UPDATE CASCADE ON DELETE CASCADE
) TYPE=InnoDB;

[Trilogen]

now I get:

[root@localhost:3306] ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'user(uid) ON UPDATE CASCADE ON DELETE CASCADE

[Trilogen]

I tried it without the foreign key line and it worked. So what's wrong with my foreign key line?

FOREIGN KEY (uid) REFERENCES user(uid) ON UPDATE CASCADE ON DELETE CASCADE

[drew010]

yah i dont know much about foreign keys but i looked on mysql.com manual and it looked just fine compared to the manual. maybe your version doesnt support foreign keys?

[bradgrafelman]

That would be my guess, too - if you still want to try and use the foreign key, post the version of your MySQL server for us.

[Trilogen]

It does support foreign keys its version 4.0.20a

[bradgrafelman]

Just a couple things I want to double check:

• Are both tables of type "InnoDB" ?
• Are you sure the table you're referencing is called "user" ?
• Try using new lines to see exactly where it's failing:

CREATE TABLE `msgbox` ( 
`mid` int(20) NOT NULL auto_increment, 
`uid` int(11) NOT NULL default '0', 
`to` varchar(30) NOT NULL default '', 
`from` varchar(30) NOT NULL default '', 
`subject` varchar(255) NOT NULL default '', 
`message` text NOT NULL, 
`time` timestamp NOT NULL, 
PRIMARY KEY (mid), 
FOREIGN KEY (uid) 
    REFERENCES user(uid)
    ON UPDATE CASCADE
    ON DELETE CASCADE 
) TYPE=InnoDB;

[Trilogen]

Originally posted by bradgrafelman

- Are both tables of type "InnoDB" ?
- Are you sure the table you're

Yes & Yes.

Your sql doesn't work either.

I really hate foreign keys. I was taught by my teacher to use foriegn keys and so I thought it was the only way to do what I'm trying to do, which is organize data by seperating them into different tables. For instance I want to seperate user login details in one table and user profile in another table whilst still linking them to each other, so if I delete a user in login table it will remove thier record in profile table too.

If you know a better way of doing this please explain it and perhapse show an example too.

And thank you guys for your interest in helping me out! I don't know how I'd manage without your feedback.

[bradgrafelman]

When you say "[my] sql doesn't work either," what did it say? Did it tell you which line wasn't working properly?

[Trilogen]

This is what I get:

[root@localhost:3306] ERROR 1005: Can't create table '.\msgtest\msgbox.frm' (errno: 150)

With this:

CREATE TABLE msgbox (
mid int(20) NOT NULL auto_increment,
uid int(11) NOT NULL default '0',
to varchar(30) NOT NULL default '',
from varchar(30) NOT NULL default '',
subject varchar(255) NOT NULL default '',
message text NOT NULL,
time timestamp NOT NULL,
PRIMARY KEY (mid),
FOREIGN KEY (uid) REFERENCES user(uid) ON UPDATE CASCADE ON DELETE CASCADE
) TYPE=InnoDB;

I added the `` on user because it kept giving back this error:

[root@localhost:3306] ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'user(uid) ON UPDATE CASCADE ON DELETE CASCADE
) TYPE=InnoDB' a

if it didn't have it. But now I'm getting that error at the top, so what could this be?

[bradgrafelman]

EDIT: Blanked this post because I was wrong. This site had this to share:

Error 150 means that you have wrong foreign key definitions. You must create index on [the apporpriate columns]
http://www.mysql.com/doc/en/InnoDB_foreign_key_constraints.html