MySQL Create Table Error
Can someone tell me what I'm doing wrong? When I first tried to create this table, I got an error message pointing out two fields that looked perfectly fine to me. I couldn't figure it out, so I just deleted the fields - but it then fingered two different fields. I deleted them, and now it says there's a problem with two other fields. I don't get it. Thanks. CREATE TABLE `geog` ( `NID` INT( 3 ) NOT NULL AUTO_INCREMENT , `IDArea` CHAR( 3 ) NOT NULL , `Name` VARCHAR( 50 ) NOT NULL , `coordNS` VARCHAR( 7 ) DEFAULT NULL , `coordNS2` VARCHAR( 3 ) DEFAULT NULL , `coordEW` VARCHAR( 8 ) DEFAULT NULL , `coordEW2` VARCHAR( 3 ) DEFAULT NULL , `coordnotes` TEXT DEFAULT NULL , `location` VARCHAR( 255 ) DEFAULT NULL , `mapref` VARCHAR( 33 ) DEFAULT NULL , `areak` DECIMAL( 9, 2 ) DEFAULT NULL , `arealandk` DECIMAL( 9, 2 ) DEFAULT NULL , `areawaterk` DECIMAL( 9, 2 ) DEFAULT NULL , `areanotes` VARCHAR( 255 ) DEFAULT NULL , `aracomp` VARCHAR( 100 ) DEFAULT NULL , `borderk` INT( 6, 1 ) DEFAULT NULL , `borderstates` VARCHAR( 255 ) DEFAULT NULL , `bordernotes` TEXT DEFAULT NULL , `coastk` DECIMAL( 6.1 ) DEFAULT NULL , `coastnotes` TEXT DEFAULT NULL , `martersea` INT( 3 ) DEFAULT NULL , `marconzone` INT( 2 ) DEFAULT NULL , `marconshelf` VARCHAR( 255 ) DEFAULT NULL , `marexecon` INT( 3 ) DEFAULT NULL , `marexfish` INT( 3 ) DEFAULT NULL , `marnotes` VARCHAR( 255 ) DEFAULT NULL , `climate` VARCHAR( 255 ) DEFAULT NULL , `elevhighpt` VARCHAR( 255 ) DEFAULT NULL , `elevhighm` INT( 4 ) DEFAULT NULL , `elevlowpt` VARCHAR( 255 ) DEFAULT NULL , `elevlowm` INT( 4 ) DEFAULT NULL , `elevnotes` VARCHAR( 255 ) DEFAULT NULL , `terrain` TEXT DEFAULT NULL , `natres` TEXT DEFAULT NULL , `nathaz` TEXT DEFAULT NULL , `envagree` TEXT DEFAULT NULL , `envagree2` VARCHAR( 255 ) DEFAULT NULL , `envissues` TEXT DEFAULT NULL , `landuse` INT( 2, 2 ) DEFAULT NULL , `landusecrops` INT( 2, 2 ) DEFAULT NULL , `landuseother` INT( 2, 2 ) DEFAULT NULL , `landusenotes` VARCHAR( 100 ) DEFAULT NULL , `irrigate` INT( 6, 2 ) DEFAULT NULL , `irrigatenotes` VARCHAR( 255 ) DEFAULT NULL , `geognotes` TEXT DEFAULT NULL , PRIMARY KEY ( `NID` ) , INDEX ( `IDArea` ) ) MySQL said: Documentation #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' 1 ) DEFAULT NULL , `borderstates` VARCHAR( 255 ) DEFAULT NULL , `bordernotes`' at line 17 __ Do you Yahoo!? Yahoo! Small Business - Try our new resources site! http://smallbusiness.yahoo.com/resources/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Create Table Error
Please ignore this thread. I think my main problem is I need glasses! --- David Blomstrom [EMAIL PROTECTED] wrote: Can someone tell me what I'm doing wrong? When I first tried to create this table, I got an error message pointing out two fields that looked perfectly fine to me. I couldn't figure it out, so I just deleted the fields - but it then fingered two different fields. I deleted them, and now it says there's a problem with two other fields. I don't get it. Thanks. CREATE TABLE `geog` ( `NID` INT( 3 ) NOT NULL AUTO_INCREMENT , `IDArea` CHAR( 3 ) NOT NULL , `Name` VARCHAR( 50 ) NOT NULL , `coordNS` VARCHAR( 7 ) DEFAULT NULL , `coordNS2` VARCHAR( 3 ) DEFAULT NULL , `coordEW` VARCHAR( 8 ) DEFAULT NULL , `coordEW2` VARCHAR( 3 ) DEFAULT NULL , `coordnotes` TEXT DEFAULT NULL , `location` VARCHAR( 255 ) DEFAULT NULL , `mapref` VARCHAR( 33 ) DEFAULT NULL , `areak` DECIMAL( 9, 2 ) DEFAULT NULL , `arealandk` DECIMAL( 9, 2 ) DEFAULT NULL , `areawaterk` DECIMAL( 9, 2 ) DEFAULT NULL , `areanotes` VARCHAR( 255 ) DEFAULT NULL , `aracomp` VARCHAR( 100 ) DEFAULT NULL , `borderk` INT( 6, 1 ) DEFAULT NULL , `borderstates` VARCHAR( 255 ) DEFAULT NULL , `bordernotes` TEXT DEFAULT NULL , `coastk` DECIMAL( 6.1 ) DEFAULT NULL , `coastnotes` TEXT DEFAULT NULL , `martersea` INT( 3 ) DEFAULT NULL , `marconzone` INT( 2 ) DEFAULT NULL , `marconshelf` VARCHAR( 255 ) DEFAULT NULL , `marexecon` INT( 3 ) DEFAULT NULL , `marexfish` INT( 3 ) DEFAULT NULL , `marnotes` VARCHAR( 255 ) DEFAULT NULL , `climate` VARCHAR( 255 ) DEFAULT NULL , `elevhighpt` VARCHAR( 255 ) DEFAULT NULL , `elevhighm` INT( 4 ) DEFAULT NULL , `elevlowpt` VARCHAR( 255 ) DEFAULT NULL , `elevlowm` INT( 4 ) DEFAULT NULL , `elevnotes` VARCHAR( 255 ) DEFAULT NULL , `terrain` TEXT DEFAULT NULL , `natres` TEXT DEFAULT NULL , `nathaz` TEXT DEFAULT NULL , `envagree` TEXT DEFAULT NULL , `envagree2` VARCHAR( 255 ) DEFAULT NULL , `envissues` TEXT DEFAULT NULL , `landuse` INT( 2, 2 ) DEFAULT NULL , `landusecrops` INT( 2, 2 ) DEFAULT NULL , `landuseother` INT( 2, 2 ) DEFAULT NULL , `landusenotes` VARCHAR( 100 ) DEFAULT NULL , `irrigate` INT( 6, 2 ) DEFAULT NULL , `irrigatenotes` VARCHAR( 255 ) DEFAULT NULL , `geognotes` TEXT DEFAULT NULL , PRIMARY KEY ( `NID` ) , INDEX ( `IDArea` ) ) MySQL said: Documentation #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' 1 ) DEFAULT NULL , `borderstates` VARCHAR( 255 ) DEFAULT NULL , `bordernotes`' at line 17 __ Do you Yahoo!? Yahoo! Small Business - Try our new resources site! http://smallbusiness.yahoo.com/resources/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Make Yahoo! your home page http://www.yahoo.com/r/hs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql create table error
Hi! You have not created an index on your foreign key. From http://www.innodb.com/ibman.html : ... An example: CREATE TABLE parent(id INT NOT NULL, PRIMARY KEY (id)) TYPE=INNODB; CREATE TABLE child(id INT, parent_id INT, INDEX par_ind (parent_id), FOREIGN KEY (parent_id) REFERENCES parent(id)) TYPE=INNODB; Both tables have to be InnoDB type and there must be an index where the foreign key and the referenced key are listed as the first columns. InnoDB does not auto-create indexes on foreign keys or referenced keys: you have to create them explicitly. If MySQL gives the error number 1005 from a CREATE TABLE statement, and the error message string refers to errno 150, then the table creation failed because a foreign key constraint was not correctly formed. . Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB Demirchyan Oganes-AOD098 wrote in message ... I have the following create table, and I get the following error when I create it. It used to work before. Can anyone help? CREATE TABLE PROTOCOL_GROUPING( PROTOCOL_GROUPING_ID MEDIUMINT(20), GROUP_NAMEVARCHAR(50)NOT NULL, PROTOCOL_ID MEDIUMINT(20) NOT NULL, PROTOCOL_STEP_NO MEDIUMINT(10) NOT NULL, foreign key (PROTOCOL_ID, PROTOCOL_STEP_NO) REFERENCES PROTOCOL_STEP(PROTOCOL_ID, PROTOCOL_STEP_NO), UNIQUE INDEX PROTOCOL_GROUPING_PK (PROTOCOL_GROUPING_ID))Type=InnoDB; I get the following error: can't create table .\test\protocol_grouping.frm (errno 150) Oganes Demirchyan Oganes Demirchyan Motorola Life Science 757 S.Raymond Pasadena, CA 91105 Tel: 626-584-5900 email: [EMAIL PROTECTED] -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Monday, February 04, 2002 11:02 AM To: Demirchyan Oganes-AOD098 Subject: Re: mysql create table error Your message cannot be posted because it appears to be either spam or simply off topic to our filter. To bypass the filter you must include one of the following words in your message: sql,query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysql create table error
Oganes, I have the following create table, and I get the following error when I create it. It used to work before. Can anyone help? CREATE TABLE PROTOCOL_GROUPING( PROTOCOL_GROUPING_ID MEDIUMINT(20), GROUP_NAMEVARCHAR(50)NOT NULL, PROTOCOL_ID MEDIUMINT(20) NOT NULL, PROTOCOL_STEP_NO MEDIUMINT(10) NOT NULL, foreign key (PROTOCOL_ID, PROTOCOL_STEP_NO) REFERENCES PROTOCOL_STEP(PROTOCOL_ID, PROTOCOL_STEP_NO), UNIQUE INDEX PROTOCOL_GROUPING_PK (PROTOCOL_GROUPING_ID))Type=InnoDB; I get the following error: can't create table .\test\protocol_grouping.frm (errno 150) =it works perfectly for me... =did you remove the existing tbl before attempting to re-run the CREATE? (I try to remember to put: DROP TABLE IF EXISTS tblNm; in every create .sql) =dn - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
