MySQL Forums
Forum List  »  Newbie

Foreign keys for primary key
Posted by: sandeep nandha
Date: July 22, 2014 09:35AM

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

Options: ReplyQuote


Subject
Written By
Posted
Foreign keys for primary key
July 22, 2014 09:35AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.