InnoDB Documentation Supports transactions, row-level locking, and foreign keys

Question

I am trying to create the following tables in phpmyadmin and I get the above error

-- ---------------------------------------------------
-- Create County Table
-- ---------------------------------------------------

CREATE  TABLE IF NOT EXISTS SecretGarden.County (
        co_id varchar (8) NOT NULL UNIQUE ,
        co_desc VARCHAR(50),
        st_date DATE,
        end_date DATE,
        PRIMARY KEY (co_id) );

-- ---------------------------------------------------
-- Create Login Table
-- ---------------------------------------------------

CREATE  TABLE IF NOT EXISTS SecretGarden.login (
        email_id varchar (100) NOT NULL UNIQUE,
        password VARCHAR(50),
        st_date DATE,
        end_date DATE,
        PRIMARY KEY (email_id) );

-- ---------------------------------------------------
-- Create Address Table
-- ---------------------------------------------------
CREATE  TABLE IF NOT EXISTS SecretGarden.address (
        add_id int (8) NOT NULL AUTO_INCREMENT ,
         address1 VARCHAR(50),
                 address2 VARCHAR(50),
                 town VARCHAR(50),
                 co_id varchar (8),
         st_date DATE,
        end_date DATE,
        PRIMARY KEY (add_id),
                CONSTRAINT fk_coid
    FOREIGN KEY (co_id)
    REFERENCES county(co_id)); 

-- ---------------------------------------------------
-- Create StaffRole Table
-- ---------------------------------------------------

CREATE  TABLE IF NOT EXISTS SecretGarden.staffrole (
        staffrole_id int (8) NOT NULL AUTO_INCREMENT ,
         staffrole_desc VARCHAR(50),
                 st_date DATE,
         end_date DATE,
        PRIMARY KEY (staffrole_id));

All of the above run perfect but when i try to create the next table i get the above error

-- ---------------------------------------------------
-- Create Staff Table
-- ---------------------------------------------------

CREATE  TABLE IF NOT EXISTS SecretGarden.staff (
        staff_id int (8) NOT NULL AUTO_INCREMENT ,
         fname VARCHAR(50),
                 lname VARCHAR(50),
                 email_id varchar (100),
                 home_no INT (20),
                 mobile_no INT (20),
                 add_id INT (8),
                 staffrole_id INT (8),
         st_date DATE,
         end_date DATE,
        PRIMARY KEY (staff_id),
                CONSTRAINT fk_emailid
        FOREIGN KEY (email_id)
            REFERENCES SecretGarden.login(email_id),
            CONSTRAINT fk_addid
                FOREIGN KEY (add_id)
                REFERENCES SecretGarden.address(add_id),
            CONSTRAINT fk_staffrole
                FOREIGN KEY (staffrole_id)
                REFERENCES SecretGarden.staffrole(staffrole_id));

Any help would be much appreciated Rachael

are your tables Innodb? default storage engine before MySQL 5.6 is MyISAM. Make sure you specify Innodb in your create table statements. as innodb only supports foreign key constraints. Basically add ENGINE=InnoDB at the end of each create table statement — Sam

Rachsherry Rachsherry · Accepted Answer · 2014-03-23T10:39:28

The problem was that I was not giving each constraint a separate name
I did not realize that they were unique for the whole database

InnoDB Documentation Supports transactions, row-level locking, and foreign keys

1 Answers