SitePoint Sponsor

User Tag List

Results 1 to 9 of 9

Hybrid View

  1. #1
    SitePoint Zealot
    Join Date
    Jul 2007
    Posts
    170
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Can't create table

    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!
    I Dunno LOL \(_o)/

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by justspree View Post
    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)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Jul 2007
    Posts
    170
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    --
    -- 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.
    I Dunno LOL \(_o)/

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot
    Join Date
    Jul 2007
    Posts
    170
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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`) );
    Quote Originally Posted by r937 View Post
    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?

    Quote Originally Posted by r937 View Post
    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.

    Quote Originally Posted by r937 View Post
    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?
    I Dunno LOL \(_o)/

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by justspree View Post
    Hmm I thought its a good practice to have its own auto increment for all tables?
    nope, that's an urban myth
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Zealot
    Join Date
    Jul 2007
    Posts
    170
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?
    I Dunno LOL \(_o)/

  8. #8
    SitePoint Zealot
    Join Date
    Jul 2007
    Posts
    170
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    I Dunno LOL \(_o)/

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    omg, it's a reserved word

    who knew, eh
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •