INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Foreign keys on Composite Primary Key - Mysql 5.6

Foreign keys on Composite Primary Key - Mysql 5.6

Foreign keys on Composite Primary Key - Mysql 5.6

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close