phpMyAdmin error 1005

Question

this is my code...i have no idea what i am doing wrong because i am new to this. i am getting an 1005 error saying that i cannot create the table bookauthor.

CREATE TABLE Book_Author(
    BID INTEGER(7) NOT NULL,
    B_TITLE VARCHAR(25) NOT NULL,
    AID INTEGER(7) NOT NULL,
    A_NAME VARCHAR(25) NOT NULL,
        CONSTRAINT PRIMARY KEY(BID),
        CONSTRAINT BID_FK
          FOREIGN KEY(BID)
          REFERENCES Book_Info(BID)
          ON DELETE CASCADE
);

Answer 1

You should try it following way

CREATE TABLE IF NOT EXISTS `Book_Author` (
  `BID` int(7) NOT NULL,
  `B_TITLE` varchar(25) NOT NULL,
  `AID` int(7) NOT NULL,
  `A_NAME` varchar(25) NOT NULL,
  PRIMARY KEY (`BID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Then use CASCADE

ALTER TABLE `Book_Author`
      ADD CONSTRAINT `BookAuthor` FOREIGN KEY (`BID`) 
REFERENCES Book_Info(BID) ON DELETE CASCADE;

Answer 2

1005 (ER_CANT_CREATE_TABLE)

Cannot create table. If the error message refers to error 150, table creation failed because a foreign key constraint was not correctly formed. If the error message refers to error –1, table creation probably failed because the table includes a column name that matched the name of an internal InnoDB table.

My guess would be that this is because you haven't created a PRIMARY KEY for you Book_Info table. You are trying to create a foreign key constraint and that requires that both table has valid index. You can add a primary key on your Book_Info table like this:

ALTER TABLE Book_Info ADD PRIMARY KEY BID_PK(BID);

Another thing that @NoDisplayName pointed out is that you should check so the two columns is of the same type and length.

You could check the status of your engine and try to find out more about this error:

SHOW ENGINE INNODB STATUS\G

Then you might see a more descriptive error message like this:

------------------------ LATEST FOREIGN KEY ERROR ------------------------
2015-02-21 05:13:34 1116b0000 Error in foreign key constraint of table test/book_author:
FOREIGN KEY(BID) REFERENCES Book_Info(BID) ON DELETE CASCADE ):
Cannot find an index in the referenced table where the referenced columns appear as the first columns, or column types in the table and the referenced table do not match for constraint.
Note that the internal storage type of ENUM and SET changed in tables created with >= InnoDB-4.1.12, and such columns in old tables cannot be referenced by such columns in new tables.
See http://dev.mysql.com/doc/refman/5.6/en/innodb-foreign-key-constraints.html for correct foreign key definition.

Answer 3

There are two possibilities in your case

You might not have used the same data type as the parent table column (ex: parent table BID is varchar and in the child table BID is Integer(7))

or Referenced column is not a primary key on the parent table

Try this

create table Book_Info(BID INTEGER(7) NOT NULL,
                      CONSTRAINT PRIMARY KEY(BID));

CREATE TABLE Book_Author(
    BID INTEGER(7) NOT NULL,
    B_TITLE VARCHAR(25) NOT NULL,
    AID INTEGER(7) NOT NULL,
    A_NAME VARCHAR(25) NOT NULL,
        CONSTRAINT PRIMARY KEY(BID),
        CONSTRAINT BID_FK
          FOREIGN KEY(BID)
          REFERENCES Book_Info(BID)
          ON DELETE CASCADE
);

• Sample Demo

Answer 4

It is important that you have the right privileges to access the database. You should ask your system administrator to give privileges to your access and if you are the admin, then make sure you are doing this action as root

Answer 5

#1005 error Solved :- Parent Table// CREATE TABLE gender(

 id int not null AUTO_INCREMENT PRIMARY KEY,
 name varchar(50)

);

Child Table// CREATE TABLE students (

id int not null AUTO_INCREMENT PRIMARY KEY,
name varchar(50),
fatherName varchar(50),
gender_id int not null,
CONSTRAINT fk_gender_id FOREIGN KEY (gender_id) REFERENCES gender(id) on DELETE SET null

); In Child table you write not null means that this column does not allow null values and in foreign key you are saying that after deleting record from parent table put NULL in column (gender_id) but that column will not allow NULL values that's why error #1005 occurs :)