InnoDB 文档支持事务、行级锁定和外键

Question

我正在尝试在phpmmyadmin中创建下表，但我得到上面的错误

-- ---------------------------------------------------
-- 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));

以上所有内容都运行完美，但是当我尝试创建下一个表时，我得到了上面的错误

-- ---------------------------------------------------
-- 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)); 

任何帮助都将不胜感激，瑞秋

Answer 1

问题是我没有给每个约束一个单独的名称
我没有意识到它们对于整个数据库是唯一的