Can't create table

[justspree]

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

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!

[r937]

Hence i decided to drop both the tables and try recreating again

that's a great place to start from, it makes things a lot simpler

please show the CREATE TABLE statement(s)

[justspree]

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

[r937]

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

[justspree]

i see two different message tables

you are going to create only the second one?

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`) );

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

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

also, i'm seeing a red flag with the "userIDs" column -- what's that for, and why?

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.

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

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

[justspree]

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

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?

[justspree]

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.

[r937]

omg, it's a reserved word

who knew, eh

[r937]

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

nope, that's an urban myth