...

View Full Version : 150 error when trying to create a table



ninnypants
03-03-2009, 05:42 AM
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.



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;

Old Pedant
03-03-2009, 07:26 AM
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.

bazz
03-03-2009, 07:31 AM
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

ninnypants
03-03-2009, 03:16 PM
Here's my question table sql:


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.


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

guelphdad
03-03-2009, 03:18 PM
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?

bazz
03-03-2009, 04:18 PM
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.

ninnypants
03-04-2009, 02:55 PM
Ok here's the entire dump minus the values, so that I don't miss anything.


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

bazz
03-04-2009, 06:56 PM
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.



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

ninnypants
03-04-2009, 11:01 PM
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.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum