Foreign keys on Composite Primary Key - Mysql 5.6

[sanei05]

Hi, I am a newbie to Mysql development and I am trying to create a tables with referential integrity between them.

When I am trying to create a foreign key constraint, I am ending up with below error. Have provided the DML statements.


Kindly advise.


Parent table with primary key on user_id and employee_id where user_id column is with auto increment of 1.


CREATE TABLE IF NOT EXISTS staff_login (
`employee_id` VARCHAR(10) NOT NULL,
`user_id` INT(10) NOT NULL AUTO_INCREMENT,
`username` VARCHAR(12) NOT NULL,
`password` VARCHAR(20) NULL,
`email` VARCHAR(35) NOT NULL,
`email_send_flag` VARCHAR(1) NULL,
`last_accessed_time` TIMESTAMP NULL,
`ip_address` CHAR(15) NULL,
`user_account_status` VARCHAR(10) NULL,
`user_mac_address` CHAR(20) NULL,
`location_code` CHAR(5) NULL,
`login_date_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
`end_date_Time` TIMESTAMP NULL,
`last_modified_by` VARCHAR(12) NULL,
`last_modified_date` DATETIME NULL,
`valid_from_date` TIMESTAMP NULL,
`expiry_date` TIMESTAMP NULL,
UNIQUE INDEX `email_UNIQUE` (`email` ASC),
UNIQUE INDEX `username_staff_log_U` (`username` ASC),
PRIMARY KEY (`user_id`, `employee_id`),
UNIQUE INDEX `employee_id_staff_log_u` (`employee_id` ASC))
ENGINE = InnoDB
AUTO_INCREMENT = 1;


Now I want to make a reference to employee id in the child table as below.
CREATE TABLE IF NOT EXISTS `adminuser`.`staff_details` (
`employee_id` VARCHAR(10) NOT NULL,
`username` VARCHAR(12) NOT NULL,
`user_type` VARCHAR(8) NOT NULL,
`email` VARCHAR(35) NOT NULL,
`password` VARCHAR(20) NOT NULL,
`create_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`Firstname` VARCHAR(20) NOT NULL,
`Lastname` VARCHAR(20) NOT NULL,
`gender` VARCHAR(6) NOT NULL,
`date_of_birth` DATE NOT NULL,
`Mobile_number` DECIMAL(10,0) NOT NULL,
`Phone Number` DECIMAL(10,0) NOT NULL,
`marital_status` VARCHAR(10) NOT NULL,
`address_line_1` VARCHAR(32) NOT NULL,
`address_line_2` VARCHAR(32) NOT NULL,
`address_line_3` VARCHAR(32) NOT NULL,
`area` VARCHAR(32) NOT NULL,
`city` VARCHAR(20) NOT NULL,
`Pincode` INT NOT NULL,
`country` VARCHAR(20) NOT NULL,
UNIQUE INDEX `username_UNIQUE` (`username` ASC),
UNIQUE INDEX `Mobile_number_UNIQUE` (`Mobile_number` ASC),
UNIQUE INDEX `userid_UNIQUE` (`employee_id` ASC),
UNIQUE INDEX `email_UNIQUE` (`email` ASC),
PRIMARY KEY (`employee_id`),
CONSTRAINT `emp_id_fk`
FOREIGN KEY (`employee_id`)
REFERENCES `adminuser`.`staff_login` (`employee_id`)
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB;


But when I try to create, I get

ERROR 1215 (HY000): Cannot add foreign key constraint