Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 9 of 9
  1. #1
    Regular Coder ninnypants's Avatar
    Join Date
    Apr 2008
    Location
    Utah
    Posts
    504
    Thanks
    10
    Thanked 47 Times in 47 Posts

    150 error when trying to create a table

    I'm trying to create a table that uses cascade delete, but every time I try to create the table I get a 150 error that says the table cannot be created. Here is the code that I'm using for one of the tables. To the best of my knowledge it doesn't have any syntax errors.

    Code:
    CREATE TABLE answers (
      answer_id int(11) NOT NULL auto_increment,
      question_id int(11) NOT NULL default '0',
      answer text NOT NULL,
      PRIMARY KEY  (answer_id),
      KEY question_id (question_id,answer_id),
      FOREIGN KEY (`question_id`) REFERENCES `survey.questions` (`question_id`) ON DELETE CASCADE
    ) TYPE=InnoDB;

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,965
    Thanks
    79
    Thanked 4,429 Times in 4,394 Posts
    PURE GUESS...

    How can question_id have a default of zero and *still* be a foreign key that references another table's (assumed primary key) field???

    By definition, a zero in that field would be illegal.

    I would definitely drop the default on that field.

  • #3
    Master Coder
    Join Date
    Apr 2003
    Location
    in my house
    Posts
    5,211
    Thanks
    39
    Thanked 201 Times in 197 Posts
    can we see the other table, surveys? If you have a data type difference on to related columns, it may drop out. that's my guess so far.

    Don't forget that you do not need (11) in your int columns. you don;t need any number unless you are zero-filling.

    Not familiar with your project but, is there always just one answer to a question? if not, maybe you could have table 'answers', table 'question' and a table to relate them?

    I would agree with Old Pedant about the zero value. if you have no zero value in the parent tbale, then you can;t have one in the child table ~ without getting a 150 error.

    bazz
    "The day you stop learning is the day you become obsolete"! - my late Dad.

    Why do some people say "I don't know for sure"? If they don't know for sure then, they don't know!
    Useful MySQL resource
    Useful MySQL link

  • #4
    Regular Coder ninnypants's Avatar
    Join Date
    Apr 2008
    Location
    Utah
    Posts
    504
    Thanks
    10
    Thanked 47 Times in 47 Posts
    Here's my question table sql:
    Code:
    CREATE TABLE questions (
      question_id int(11) NOT NULL auto_increment,
      question text NOT NULL,
      PRIMARY KEY  (question_id)
    ) TYPE=InnoDB;
    This table went in fine, and the only other tables that are getting the 150 are tables with the cascade delete. I've removed all defaults that would interfere with things working correctly, but that hasn't helped.
    Code:
    CREATE TABLE responses (
      user_id int(11) NOT NULL,
      answer_id int(11) NOT NULL,
      PRIMARY KEY  (user_id,answer_id),
      KEY answer_id (answer_id),
      FOREIGN KEY (`user_id`) REFERENCES `survey.users` (`user_id`) ON DELETE CASCADE,
      FOREIGN KEY (`answer_id`) REFERENCES `survey.answers` (`answer_id`) ON DELETE CASCADE
    ) TYPE=InnoDB;
    
    CREATE TABLE winners (
      weekdate datetime NOT NULL default '0000-00-00 00:00:00',
      user_id int(11) NOT NULL,
      claim_code char(8) NOT NULL default '',
      notify_dt datetime default NULL,
      claim_dt datetime default NULL,
      confirm_dt datetime default NULL,
      PRIMARY KEY  (weekdate),
      UNIQUE KEY claim_code (claim_code),
      KEY user_id (user_id),
      FOREIGN KEY (`user_id`) REFERENCES `survey.users` (`user_id`) ON DELETE CASCADE
    ) TYPE=InnoDB;
    All of this is from a sql dump from a book on advanced mysql/php

  • #5
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    you haven't shown us the survey.users or survey.answers tables. Are the data types the same in the columns you are referencing in the foreign key and the primary table you are referring to?

  • #6
    Master Coder
    Join Date
    Apr 2003
    Location
    in my house
    Posts
    5,211
    Thanks
    39
    Thanked 201 Times in 197 Posts
    just a few things before I switch off. been working since 10pm last night and its now 4.15pm.

    You don't need (11) for int unless you are using zerofill.
    In table responses, you have a composite PK. So if I remember correctly, your foreign key has to be both cols.
    IN the winners table, I would be inclined to consider using the claim code as the PK. It will always be unique whereas ~ if you change things ~ the weekdate may not be.
    OH yeh and, char is I think for values of 3 or less. if you tell it to be varchar (3) I think it changes to char. char (8) should be varchar(8).

    But we need to see the other tables to give definitive answerssszzzz

    nite nite.
    "The day you stop learning is the day you become obsolete"! - my late Dad.

    Why do some people say "I don't know for sure"? If they don't know for sure then, they don't know!
    Useful MySQL resource
    Useful MySQL link

  • #7
    Regular Coder ninnypants's Avatar
    Join Date
    Apr 2008
    Location
    Utah
    Posts
    504
    Thanks
    10
    Thanked 47 Times in 47 Posts
    Ok here's the entire dump minus the values, so that I don't miss anything.
    Code:
    -- MySQL dump 9.06
    --
    -- Host: localhost    Database: survey
    ---------------------------------------------------------
    -- Server version	4.0.3-beta-log
    
    --
    -- Table structure for table 'admin'
    --
    
    CREATE TABLE admin (
      username varchar(50) NOT NULL default '',
      password varchar(255) NOT NULL default '',
      PRIMARY KEY  (username)
    ) TYPE=InnoDB;
    
    
    --
    -- Table structure for table 'age_ranges'
    --
    
    CREATE TABLE age_ranges (
      min_age int(11) NOT NULL default '0',
      max_age int(11) NOT NULL default '0',
      age_range varchar(10) default NULL,
      PRIMARY KEY  (min_age,max_age)
    ) TYPE=InnoDB;
    
    
    --
    -- Table structure for table 'answers'
    --
    
    CREATE TABLE answers (
      answer_id int(11) NOT NULL auto_increment,
      question_id int(11) NOT NULL default '0',
      answer text NOT NULL,
      PRIMARY KEY  (answer_id),
      KEY question_id (question_id,answer_id),
      FOREIGN KEY (`question_id`) REFERENCES `survey.questions` (`question_id`) ON DELETE CASCADE
    ) TYPE=InnoDB;
    
    
    --
    -- Table structure for table 'blocked_domains'
    --
    
    CREATE TABLE blocked_domains (
      domain varchar(30) NOT NULL default '',
      block_by varchar(50) default NULL,
      block_dt datetime NOT NULL default '0000-00-00 00:00:00',
      release_dt datetime default NULL,
      notes text,
      modify_dt timestamp(14) NOT NULL,
      PRIMARY KEY  (domain),
      KEY block_by (block_by),
      FOREIGN KEY (`block_by`) REFERENCES `survey.admin` (`username`) ON DELETE SET NULL
    ) TYPE=InnoDB;
    
    
    --
    -- Table structure for table 'questions'
    --
    
    CREATE TABLE questions (
      question_id int(11) NOT NULL auto_increment,
      question text NOT NULL,
      PRIMARY KEY  (question_id)
    ) TYPE=InnoDB;
    
    
    --
    -- Table structure for table 'responses'
    --
    
    CREATE TABLE responses (
      user_id int(11) NOT NULL default '0',
      answer_id int(11) NOT NULL default '0',
      PRIMARY KEY  (user_id,answer_id),
      KEY answer_id (answer_id),
      FOREIGN KEY (`user_id`) REFERENCES `survey.users` (`user_id`) ON DELETE CASCADE,
      FOREIGN KEY (`answer_id`) REFERENCES `survey.answers` (`answer_id`) ON DELETE CASCADE
    ) TYPE=InnoDB;
    
    
    --
    -- Table structure for table 'states'
    --
    
    CREATE TABLE states (
      state char(2) NOT NULL default '',
      statename varchar(30) NOT NULL default '',
      PRIMARY KEY  (state)
    ) TYPE=InnoDB;
    
    
    --
    -- Table structure for table 'users'
    --
    
    CREATE TABLE users (
      user_id int(11) NOT NULL auto_increment,
      name varchar(50) default NULL,
      email varchar(50) default NULL,
      country varchar(20) default NULL,
      state char(2) default NULL,
      age int(11) default NULL,
      remote_addr varchar(15) default NULL,
      remote_host varchar(80) default NULL,
      create_dt timestamp(14) NOT NULL,
      PRIMARY KEY  (user_id)
    ) TYPE=InnoDB;
    
    
    --
    -- Table structure for table 'winners'
    --
    
    CREATE TABLE winners (
      weekdate datetime NOT NULL default '0000-00-00 00:00:00',
      user_id int(11) NOT NULL default '0',
      claim_code char(8) NOT NULL default '',
      notify_dt datetime default NULL,
      claim_dt datetime default NULL,
      confirm_dt datetime default NULL,
      PRIMARY KEY  (weekdate),
      UNIQUE KEY claim_code (claim_code),
      KEY user_id (user_id),
      FOREIGN KEY (`user_id`) REFERENCES `survey.users` (`user_id`) ON DELETE CASCADE
    ) TYPE=InnoDB;
    I haven't had any problems with tables that don't use a foreign key/cascade delete. The only thing it has really told me is that it cannot create the table. The error never gave the reason it could not. I left the defaults that were suggested to be taken out in this one because removing them had no effect.

  • #8
    Master Coder
    Join Date
    Apr 2003
    Location
    in my house
    Posts
    5,211
    Thanks
    39
    Thanked 201 Times in 197 Posts
    OK, I am not au fait with mysql 4.0 so some of this may need to be adapted since I work with mysql 5. Specifically CONSTRAINTS - in mysql 5 there is another bit of text required, from what you have got here.

    Code:
    CREATE TABLE admin (
      username varchar(50) NOT NULL default '',
      password varchar(255) NOT NULL default '',
      PRIMARY KEY  (username)
    ) TYPE=InnoDB;
    
    -- why so many chars in the password.  255 is a lot to type in. so is 50 for the username. and if there is no foreign key there - or to that table - you could use MyIsam as the storage engine. 
    
    
    --
    -- Table structure for table 'age_ranges'
    --
    
    CREATE TABLE age_ranges (
      min_age int(11) NOT NULL default '0',
      max_age int(11) NOT NULL default '0',
      age_range varchar(10) default NULL,
      PRIMARY KEY  (min_age,max_age)
    ) TYPE=InnoDB;
    
    -- age range should have at most three chars/digits?  OK then so TINYINT would be more appropriate for min and max.  not sure what you plan to put into age_range so I can't comment. 
    --
    -- Table structure for table 'answers'
    --
    
    CREATE TABLE answers (
      answer_id int(11) NOT NULL auto_increment,
      question_id int(11) NOT NULL default '0',
      answer text NOT NULL,
      PRIMARY KEY  (answer_id),
      KEY question_id (question_id,answer_id),
      CONSTRAINT answer_questions_fk,
        FOREIGN KEY (`question_id`) 
          REFERENCES `survey.questions` (`question_id`) ON DELETE CASCADE
    ) TYPE=InnoDB;
    
    
    --
    -- Table structure for table 'blocked_domains'
    --
    
    CREATE TABLE blocked_domains (
      domain varchar(30) NOT NULL default '',
      block_by varchar(50) default NULL,
      block_dt datetime NOT NULL default '0000-00-00 00:00:00',
      release_dt datetime default NULL,
      notes text,
      modify_dt timestamp(14) NOT NULL,
      PRIMARY KEY  (domain),
      KEY block_by (block_by),
      FOREIGN KEY (`block_by`) REFERENCES `survey.admin` (`username`) ON DELETE SET NULL
    ) TYPE=InnoDB;
    
    -- why 30 for the domain?
    -- I would use timestamp for block_dt and release_dt just like you have done for modify_dt. (not sure the '14' is needed. check the docs).
    --
    
      
    --
    -- Table structure for table 'questions'
    --
    
    CREATE TABLE questions (
      question_id int(11) NOT NULL auto_increment,
      question text NOT NULL,
      PRIMARY KEY  (question_id)
    ) TYPE=InnoDB;
    
    --  
    --
    -- Table structure for table 'responses'
    --
    
    CREATE TABLE responses (
      user_id int(11) NOT NULL default '0',
      answer_id int(11) NOT NULL default '0',
      PRIMARY KEY  (user_id,answer_id),
      KEY answer_id (answer_id),
      FOREIGN KEY (`user_id`) REFERENCES `survey.users` (`user_id`) ON DELETE CASCADE,
      FOREIGN KEY (`answer_id`) REFERENCES `survey.answers` (`answer_id`) ON DELETE CASCADE
    ) TYPE=InnoDB;
    
    
    --
    -- Table structure for table 'states'
    --
    
    CREATE TABLE states (
      state char(2) NOT NULL default '',
      statename varchar(30) NOT NULL default '',
      PRIMARY KEY  (state)
    ) TYPE=InnoDB;
    
    
    --
    -- Table structure for table 'users'
    --
    
    CREATE TABLE users (
      user_id int(11) NOT NULL auto_increment,
      name varchar(50) default NULL,
      email varchar(50) default NULL,
      country varchar(20) default NULL,
      state char(2) default NULL,
      age int(11) default NULL,
      remote_addr varchar(15) default NULL,
      remote_host varchar(80) default NULL,
      create_dt timestamp(14) NOT NULL,
      PRIMARY KEY  (user_id)
    ) TYPE=InnoDB;
    
    -- I would remove the (nn) for int.
    -- I would suggest you think about how many chars might need to be entered. email address can have more than 50.  country can have more than 20. 
    --
    -- Table structure for table 'winners'
    --
    
    CREATE TABLE winners (
      weekdate datetime NOT NULL default '0000-00-00 00:00:00',
      user_id int(11) NOT NULL default '0',
      claim_code char(8) NOT NULL default '',
      notify_dt datetime default NULL,
      claim_dt datetime default NULL,
      confirm_dt datetime default NULL,
      PRIMARY KEY  (weekdate),
      UNIQUE KEY claim_code (claim_code),
      KEY user_id (user_id),
      FOREIGN KEY (`user_id`) REFERENCES `survey.users` (`user_id`) ON DELETE CASCADE
    ) TYPE=InnoDB;
    
    -- char(8) is too high. char is for lees than 4 (iirc)
    I only added CONSTRAINT once to let you see how it is done but , again, I do not know if it is supported in mysql 4.

    And I haven't checked for normalisation issues, because my brain is on low power today.

    bazz
    "The day you stop learning is the day you become obsolete"! - my late Dad.

    Why do some people say "I don't know for sure"? If they don't know for sure then, they don't know!
    Useful MySQL resource
    Useful MySQL link

  • #9
    Regular Coder ninnypants's Avatar
    Join Date
    Apr 2008
    Location
    Utah
    Posts
    504
    Thanks
    10
    Thanked 47 Times in 47 Posts
    what exactly does CONSTRAINT do. I think I may be using mysql 5. The book is kind of old, so it's probably an issue of the mysql version.


  •  

    Posting Permissions

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