CODEWITHSUNDEEP
mysqlsqlmysql-error-1005
Iza_U
Iza_U

Reputation: 57

MySQL Error 1005 on Create Table

I was creating a new database on MySQL Workbench, but I get a lot of errors. So I tried create manually. But I get another error too, I can't understand what is happening.

That's my SQL code:

CREATE DATABASE data;

USE data;

CREATE  TABLE IF NOT EXISTS `TB_CHILD` (
 `ID` INT NOT NULL AUTO_INCREMENT ,
 `NAME` VARCHAR(250) NOT NULL ,
 `STATUS` ENUM('A','I') NOT NULL ,
 PRIMARY KEY (`ID`) )
ENGINE = InnoDB;

CREATE  TABLE IF NOT EXISTS `TB_PARENT` (
 `ID` INT NOT NULL AUTO_INCREMENT ,
 `TITLE` VARCHAR(250) NOT NULL ,
 `CHILD` INT NOT NULL ,
 `STATUS` ENUM('A','I') NOT NULL ,
 PRIMARY KEY (`ID`) ,
 INDEX `FK_PARENT_CHILD` (`CHILD` ASC) ,
 CONSTRAINT `FK_PARENT_CHILD`
   FOREIGN KEY (`CHILD` )
   REFERENCES `TB_CHILD` (`ID` ))
ENGINE = InnoDB;

I always get the error code 1005. I'm using Mysql Server 5.5. What's wrong with my code?

EDIT: Updated with the code USE data; But I still get the same error.

Upvotes: 0

Views: 169

Answers (3)

Euclides Mulémbwè
Euclides Mulémbwè

Reputation: 1687

you a trying to create an index to a field that doesn't exist yet:

INDEX `FK_PARENT_CHILD` (`CHILD` ASC) ,
 CONSTRAINT `FK_PARENT_CHILD`
   FOREIGN KEY (`CHILD` )
   REFERENCES `TB_CHILD` (`ID` ))

try to revert the order:

CONSTRAINT `FK_PARENT_CHILD`
   FOREIGN KEY (`CHILD` )
   REFERENCES `TB_CHILD` (`ID` )),
INDEX `FK_PARENT_CHILD` (`CHILD` ASC)

Upvotes: 2

Aneesh Narayanan
Aneesh Narayanan

Reputation: 3434

Use the created data base "data".

use data;

Your database needs to be created only once, but you must select it for use each time you begin a mysql session.if you did not selected the data base , the table creation fails. So that you are getting the error 1005.

Upvotes: 2

Euclides Mulémbwè
Euclides Mulémbwè

Reputation: 1687

you created the database but you didn't select it to create the tables, try:

CREATE DATABASE data;

use data;

CREATE  TABLE IF NOT EXISTS `TB_CHILD` (
...

Upvotes: 1

Related Questions