Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm trying to create the following schema, but I'm getting errors when I'm trying to create project and staffphone tables.

Not sure what is it complaining about.

mysql> CREATE TABLE staff(
-> StaffID INT AUTO_INCREMENT PRIMARY KEY,
-> FirstName VARCHAR(15) NOT NULL,
-> LastName VARCHAR(15) NOT NULL,
-> Address VARCHAR(30) ,
-> City VARCHAR(15) ,
-> State VARCHAR(3) ,
-> Position VARCHAR(20) NOT NULL,
-> Wage INT NOT NULL
-> );
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE projects (
-> StaffID INT NOT NULL,
-> ProjectName VARCHAR(20) NOT NULL,
-> Allocation INT NOT NULL,
-> PRIMARY KEY (StaffID, ProjectName) ,
-> FOREIGN KEY (StaffID) REFERENCES Staff(StaffID) ON DELETE CASCADE
-> );
ERROR 1005 (HY000): Can't create table 'staff.projects' (errno: 150)

mysql> CREATE TABLE staffphone (
-> StaffID INT NOT NULL,
-> PhoneNumber VARCHAR(15) NOT NULL,
-> PRIMARY KEY (StaffID, PhoneNumber) ,
-> FOREIGN KEY (StaffID) REFERENCES Staff(StaffID) ON DELETE CASCADE
-> );
ERROR 1005 (HY000): Can't create table 'staff.staffphone' (errno: 150)
mysql>

Any idea would be helpful.

Thank you

share|improve this question
3  
The table is named staff but you have REFERENCES Staff. Is this in Linux? Read this: Identifier Case Sensitivity –  ypercube Mar 19 at 18:37
    
I have alterd the table and changes staff to Staff as you noted, and it worked, thanks for your help! –  Deano Mar 19 at 18:40
    
@ypercube you hit the nail right on the head. Post it as an answer !!! –  RolandoMySQLDBA Mar 19 at 18:40
2  
@Deano, I would keep all the table names in lowercase. –  ypercube Mar 19 at 18:43
    
Will do, thank you –  Deano Mar 19 at 18:47

1 Answer 1

up vote 4 down vote accepted

As was stated in the comments by @yercube, you have a case sensitivity issue.

SOLUTION #1

Create the first table with the name Staff

CREATE TABLE Staff(
   StaffID INT AUTO_INCREMENT PRIMARY KEY,
   FirstName VARCHAR(15) NOT NULL,
   LastName VARCHAR(15) NOT NULL,
   Address VARCHAR(30) ,
   City VARCHAR(15) ,
   State VARCHAR(3) ,
   Position VARCHAR(20) NOT NULL,
   Wage INT NOT NULL
);

Then, create the other two tables. It should work.

SOLUTION #2 (based on @ypercube's suggestion)

Keep the table staff, but create the other two like this:

CREATE TABLE projects (
    StaffID INT NOT NULL,
    ProjectName VARCHAR(20) NOT NULL,
    Allocation INT NOT NULL,
    PRIMARY KEY (StaffID, ProjectName) ,
    FOREIGN KEY (StaffID) REFERENCES staff(StaffID) ON DELETE CASCADE
 );

CREATE TABLE staffphone (
    StaffID INT NOT NULL,
    PhoneNumber VARCHAR(15) NOT NULL,
    PRIMARY KEY (StaffID, PhoneNumber) ,
    FOREIGN KEY (StaffID) REFERENCES staff(StaffID) ON DELETE CASCADE
);

Give it a Try !!!

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.