SQL CREATE TABLE - Error

Question

I am currently using phpmyadmin to create many different tables, but this one seems to be causing a problem. Could this be an error with this part of the code, or possibly from something that references Staff further down in my code?

SQL query:

CREATE TABLE Staff(

Staff_ID INTEGER( 5 ) PRIMARY KEY AUTO_INCREMENT ,
Local_ID INTEGER( 5 ) ,
First_name VARCHAR( 20 ) NOT NULL ,
Last_name VARCHAR( 20 ) NOT NULL ,
Address_line_1 VARCHAR( 30 ) NOT NULL ,
Address_line_2 VARCHAR( 30 ) NOT NULL ,
City VARCHAR( 20 ) NOT NULL ,
Post_Code VARCHAR( 8 ) NOT NULL ,
Email VARCHAR( 30 ) NOT NULL ,
Telephone INTEGER NOT NULL ,
Date_employed DATE,
Salary DECIMAL,
Sales_ID INTEGER( 5 ) ,
Manager_ID INTEGER( 5 ) ,
Development_ID INTEGER( 5 ) ,
FOREIGN KEY ( Local_ID ) REFERENCES LocalOffice( Local_ID ) ,
FOREIGN KEY ( Sales_ID ) REFERENCES Sales_Advisors( Sales_ID ) ,
FOREIGN KEY ( Manager_ID ) REFERENCES Site_Manager( Manager_ID ) ,
FOREIGN KEY ( Development_ID ) REFERENCES Development( Development_ID )
);

MySQL said:

1005 - Can't create table 'h_h.staff' (errno: 150) (Details...)

this code is working on my mysql? what's your problem? show the error please. — Vijaya Pandey, Apr 29 '13 at 06:27
`InnoDB Supports transactions, row-level locking, and foreign keys [ Variables | Buffer Pool | InnoDB Status ]` — Luke Clifford, Apr 29 '13 at 06:27
InnoDB shouldn't be the problem though, as I haven't had it applied at all yet. And Staff has worked previously. — Luke Clifford, Apr 29 '13 at 06:28
Does that from the mysql doc help you: http://dev.mysql.com/doc/refman/5.1/de/innodb-error-codes.html — MatthiasLaug, Apr 29 '13 at 06:28
Are all the referenced keys in foreign key definitions primary keys in their respective tables? — Joachim Isaksson, Apr 29 '13 at 06:30
Make sure your `FOREIGN KEY`s meet the conditions listed at - http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html — Sean, Apr 29 '13 at 06:31
From http://dev.mysql.com/doc/refman/5.1/en/innodb-error-codes.html `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.` — Sean, Apr 29 '13 at 06:33
Can you add your table structure for `LocalOffice`, `Sales_Advisors`, `Site_Manager`, and `Development`. Based off the code you posted, it should work. see sqlfiddle - http://sqlfiddle.com/#!2/ea6c5 — Sean, Apr 29 '13 at 06:48

score 2 · Accepted Answer · answered Apr 29 '13 at 06:47

2

From error, it is pretty much clear that this error is due to foreign key constrains. Can you try disabling foreign key constrain, then create your table and finally enable it back?

SET foreign_key_checks = 0;

CREATE TABLE Staff(

Staff_ID INTEGER( 5 ) PRIMARY KEY AUTO_INCREMENT ,
Local_ID INTEGER( 5 ) ,
First_name VARCHAR( 20 ) NOT NULL ,
Last_name VARCHAR( 20 ) NOT NULL ,
Address_line_1 VARCHAR( 30 ) NOT NULL ,
Address_line_2 VARCHAR( 30 ) NOT NULL ,
City VARCHAR( 20 ) NOT NULL ,
Post_Code VARCHAR( 8 ) NOT NULL ,
Email VARCHAR( 30 ) NOT NULL ,
Telephone INTEGER NOT NULL ,
Date_employed DATE,
Salary DECIMAL,
Sales_ID INTEGER( 5 ) ,
Manager_ID INTEGER( 5 ) ,
Development_ID INTEGER( 5 ) ,
FOREIGN KEY ( Local_ID ) REFERENCES LocalOffice( Local_ID ) ,
FOREIGN KEY ( Sales_ID ) REFERENCES Sales_Advisors( Sales_ID ) ,
FOREIGN KEY ( Manager_ID ) REFERENCES Site_Manager( Manager_ID ) ,
FOREIGN KEY ( Development_ID ) REFERENCES Development( Development_ID )
);

SET foreign_key_checks = 1;

answered Apr 29 '13 at 06:47

Amit

1,365
8
15

How many problems is this likely to causer later on though? – Luke Clifford Apr 29 '13 at 06:51
@LukeClifford see http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_foreign_key_checks – Sean Apr 29 '13 at 07:03
How about: This appears to work: `INSERT INTO Staff VALUES (null, null, 'Sean', 'D', '[email protected]', '07411557877', '2012-10-08', 80000.00,null,null,null);` – Luke Clifford Apr 29 '13 at 07:15
So I've seen the links you have sent me. And they have worked, but I think by using this work around it's meant that i can't use simply INSERT statements: `NSERT INTO Staff VALUES (null, null, 'Sean', 'D', '[email protected]', '07411557877', '2012-10-08', 80000.00,null,null,null);` – Luke Clifford Apr 29 '13 at 07:20

score 0 · Answer 2 · answered Apr 29 '13 at 06:42

0

I think you should create index on foreign keys like this

...

Development_ID INTEGER( 5 ) ,
INDEX Local_ID,
INDEX Sales_ID,
INDEX Manager_ID,
INDEX DEvelopment_ID,
FOREIGN KEY ( Local_ID ) REFERENCES LocalOffice( Local_ID ) ,
FOREIGN KEY ( Sales_ID ) REFERENCES Sales_Advisors( Sales_ID ) ,
FOREIGN KEY ( Manager_ID ) REFERENCES Site_Manager( Manager_ID ) ,
FOREIGN KEY ( Development_ID ) REFERENCES Development( Development_ID )
);

answered Apr 29 '13 at 06:42

Amir

4,089
4
16
28

But this hasn't been the case up until now. – Luke Clifford Apr 29 '13 at 06:44
I think before you define foreign key you should index it. and it is the reason, table is not created. – Amir Apr 29 '13 at 06:47

SQL CREATE TABLE - Error

1005 - Can't create table 'h_h.staff' (errno: 150) (Details...)

2 Answers2