Creating three related tables in MySQL 5.5

Question

Use snhpinventorytest;
CREATE TABLE Persons (
    PersonID INT(11) unsigned NOT NULL auto_increment,
        PRIMARY KEY (PersonID),
    FirstName Varchar (50), 
    Lastname varchar(50)   
)   ENGINE=INNODB;

Use snhpinventorytest;
CREATE TABLE if not exists Locations (
    LocationID INT(11) UNSIGNED NOT NULL auto_increment,
    PRIMARY KEY (LocationID),
    PersonID int(11) unsigned,
        INDEX PersonID_IDX (PersonID),
    BuildingName varchar(50),
    LocationType varchar(50),
    RoomNumber varchar (20),
        FOREIGN KEY (FK_PersonID) REFERENCES Person(PersonID)
            ON DELETE CASCADE
            ON UPDATE CASCADE
)  ENGINE=INNODB;

Use snhpinventorytest;
CREATE TABLE if not exists assets (
    AssetID INT(11)  UNSIGNED NOT NULL auto_increment,
     PRIMARY KEY (AssetID),
     PersonID int(11) unsigned,
        INDEX PersonID_IDX (PersonID),
     FOREIGN KEY (FK_PersonID) REFERENCES Person(PersonID)
        ON DELETE CASCADE
        ON UPDATE CASCADE
)   Engine=INNODB;

I am new to MySQL. Using Workbench 6.2 I can create the Person table, but not the Locations or Assets tables.
1005 and (errno 150) Suggestions appreciated. I think the order of my statements around the Foreign Key may be incorrect, but not sure how?

Answer 1

You have to change your code for it to work.

you need a CONSTRAINT name and make it something unique. Then you need to provide it the correct FOREIGN KEY and not any name. For example FOREIGN KEY (FK_PersonID) should be FOREIGN KEY (PersonID)

also, you do not need to use USE snhpinventorytest; 3 times :) you can use it once.

I hope this helps

Try this code

USE snhpinventorytest;
CREATE TABLE Persons (
    PersonID INT(11) unsigned NOT NULL  auto_increment,
        PRIMARY KEY (PersonID),
    FirstName Varchar (50), 
    Lastname varchar(50)   
)   ENGINE=INNODB;

CREATE TABLE locations (
 LocationID int(11) unsigned NOT NULL AUTO_INCREMENT,
 PersonID int(11) unsigned DEFAULT NULL,
 BuildingName varchar(50) DEFAULT NULL,
 LocationType varchar(50) DEFAULT NULL,
 RoomNumber varchar(20) DEFAULT NULL,
 PRIMARY KEY (LocationID),
 KEY PersonID_IDX (PersonID),
 CONSTRAINT FK_PersonID1 FOREIGN KEY (PersonID) REFERENCES persons (PersonID) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS assets (
    AssetID INT(11)  UNSIGNED NOT NULL auto_increment,
     PRIMARY KEY (AssetID),
     PersonID int(11) unsigned,
        INDEX PersonID_IDX (PersonID),
     CONSTRAINT FK_PersonID2 FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
        ON DELETE CASCADE
        ON UPDATE CASCADE
)   Engine=INNODB;