Can't create table

Databases
1

Hi, I recently have 2 tables named “message” and “message_content” which I wanted to alter changes. Since its a development server I can do whatever I want. Foreign keys are already set and it is complaining that I can’t alter changes. Hence i emptied all the data related in both tables and tried altering the column name from the message table, still can’t. Hence i decided to drop both the tables and try recreating again, did a search through Google and found out that I can use something like this:

set foreign_key_checks = 0;
drop table message;
set foreign_key_checks = 1;

I managed to drop both the tables but now, I can’t recreate the table name “message” anymore :frowning:

Its giving me SQL Error 1005: Can’t create table ‘message’ (errno: 150)

I have no idea how to go about this and googling didnt help much. Hope someone here with experience can help. Thanks in advanced!

2

that’s a great place to start from, it makes things a lot simpler :slight_smile:

please show the CREATE TABLE statement(s)

3

–
– Table structure for table message
–

CREATE TABLE IF NOT EXISTS message (
mID int(10) unsigned NOT NULL auto_increment,
toUserID int(10) unsigned NOT NULL,
fromUserID int(10) unsigned NOT NULL,
subject varchar(255) NOT NULL,
notification int(11) NOT NULL default ‘0’,
draft enum(‘0’,‘1’) NOT NULL default ‘0’,
archive enum(‘0’,‘1’) NOT NULL default ‘0’,
created datetime NOT NULL,
opened enum(‘0’,‘1’) NOT NULL default ‘0’,
rcptDel enum(‘0’,‘1’) NOT NULL default ‘0’,
sndDel enum(‘0’,‘1’) NOT NULL default ‘0’,
PRIMARY KEY (mID),
KEY FK_MSG_TO_USERID (toUserID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=122 ;

–
– Table structure for table message_content

CREATE TABLE IF NOT EXISTS message_content (
mtID int(10) unsigned NOT NULL auto_increment,
mID int(10) unsigned NOT NULL,
content mediumtext NOT NULL,
PRIMARY KEY (mtID),
KEY FK_MSG_MID (mID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;

–
– Constraints for table message

ALTER TABLE message
ADD CONSTRAINT FK_MSG_TO_USERID FOREIGN KEY (toUserID) REFERENCES user (userID) ON DELETE CASCADE ON UPDATE NO ACTION;

And the new table which i’m gonna create is:

CREATE TABLE message (
msgID INT(10) UNSIGNED NOT NULL AUTO_INCREMENT ,
userIDs VARCHAR(255) NOT NULL ,
subject VARCHAR(255) NOT NULL ,
created DATETIME NOT NULL ,
PRIMARY KEY (msgID) );

Thank you very much rudy. Since i’ve already dropped both the tables on my localhost, I used the development server to get the create statement for these 2 tables.

4

i see two different message tables

you are going to create only the second one?

then obviously the FK to the user table won’t work, because the second one doesn’t have a toUserID column

also, i’m seeing a red flag with the “userIDs” column – what’s that for, and why?

also, the message_content should really not have its own auto_increment column

5

Yes, the new one which I want to recreate is the bottom one, the top 2 are old tables which I’ve dropped

CREATE TABLE message (
msgID INT(10) UNSIGNED NOT NULL AUTO_INCREMENT ,
userIDs VARCHAR(255) NOT NULL ,
subject VARCHAR(255) NOT NULL ,
created DATETIME NOT NULL ,
PRIMARY KEY (msgID) );

Sorry don’t get what you mean cos I’ve already deleted the FK, then dropped both the 2 old tables?

I’m planning to store userIDs with comma delimiter like 1,2,3,4. This column is not for direct referencing to any table, it will only be loop thru my application code.

Hmm I thought its a good practice to have its own auto increment for all tables?

6

Now the weird thing is, I can recreate the message table using the old create statement :frowning:

CREATE TABLE IF NOT EXISTS message (
mID int(10) unsigned NOT NULL auto_increment,
toUserID int(10) unsigned NOT NULL,
fromUserID int(10) unsigned NOT NULL,
subject varchar(255) NOT NULL,
notification int(11) NOT NULL default ‘0’,
draft enum(‘0’,‘1’) NOT NULL default ‘0’,
archive enum(‘0’,‘1’) NOT NULL default ‘0’,
created datetime NOT NULL,
opened enum(‘0’,‘1’) NOT NULL default ‘0’,
rcptDel enum(‘0’,‘1’) NOT NULL default ‘0’,
sndDel enum(‘0’,‘1’) NOT NULL default ‘0’,
PRIMARY KEY (mID),
KEY FK_MSG_TO_USERID (toUserID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=122 ;

This is the table which I don’t want. I want to change to

CREATE TABLE message (
msgID INT(10) UNSIGNED NOT NULL AUTO_INCREMENT ,
userIDs VARCHAR(255) NOT NULL ,
subject VARCHAR(255) NOT NULL ,
created DATETIME NOT NULL ,
PRIMARY KEY (msgID) );

Is this some kind of innoDB bug?

7

Hi rudy, thanks anyway. I think its a really stupid bug. I decided to rename the message table to messages instead then. Thanks for your help anyway.

8

omg, it’s a reserved word

who knew, eh

9

nope, that’s an urban myth