ERROR: Error 1005: Can't create table (errno: 121)
I have troubles with forward engineering my MySQL database into WAMP server..
I was going to post an image of the schema but as this is my first post I can't.
Below is the executed script..
use aquaticstar;
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
-- -----------------------------------------------------
-- Table `Students`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `Students` ;
CREATE TABLE IF NOT EXISTS `Students` (
`id` VARCHAR(10) NOT NULL ,
`studentName` VARCHAR(45) NOT NULL ,
`gender` CHAR NOT NULL ,
`birthDate` DATETIME NOT NULL ,
`mNo` VARCHAR(10) NOT NULL ,
`contactName` VARCHAR(45) NOT NULL ,
`contactEmail` VARCHAR(45) NOT NULL ,
`contactPhone` INT(10) NOT NULL ,
`startDate` DATETIME NOT NULL ,
`remarks` VARCHAR(200) NULL ,
PRIMARY KEY (`id`) )
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `Waiting List`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `Waiting List` ;
CREATE TABLE IF NOT EXISTS `Waiting List` (
`wait_id` VARCHAR(5) NOT NULL ,
`name` VARCHAR(45) NULL ,
`contactName` VARCHAR(45) NULL ,
`contactPhone` INT(10) NULL ,
`contactEmail` VARCHAR(45) NULL ,
`status` CHAR NULL ,
`remarks` VARCHAR(200) NULL ,
PRIMARY KEY (`wait_id`) )
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `Schedule`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `Schedule` ;
CREATE TABLE IF NOT EXISTS `Schedule` (
`lesson_id` VARCHAR(10) NOT NULL ,
`day` VARCHAR(3) NOT NULL ,
`branch` VARCHAR(30) NOT NULL ,
`level` VARCHAR(30) NOT NULL ,
`time` TIME NOT NULL ,
`ae` VARCHAR(45) NOT NULL ,
PRIMARY KEY (`lesson_id`) )
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `Link`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `Link` ;
CREATE TABLE IF NOT EXISTS `Link` (
`link_id` VARCHAR(10) NOT NULL ,
`id` VARCHAR(10) NOT NULL ,
`lesson_id` VARCHAR(10) NOT NULL ,
PRIMARY KEY (`link_id`) ,
INDEX `id_idx` (`id` ASC) ,
INDEX `lesson_id_idx` (`lesson_id` ASC) ,
CONSTRAINT `id`
FOREIGN KEY (`id` )
REFERENCES `Students` (`id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `lesson_id`
FOREIGN KEY (`lesson_id` )
REFERENCES `Schedule` (`lesson_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `Attendance`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `Attendance` ;
CREATE TABLE IF NOT EXISTS `Attendance` (
`date` DATETIME NOT NULL ,
`attendance` VARCHAR(5) NOT NULL ,
`link_id` VARCHAR(10) NOT NULL ,
INDEX `link_id_idx` (`link_id` ASC) ,
CONSTRAINT `link_id`
FOREIGN KEY (`link_id` )
REFERENCES `Link` (`link_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
-- -----------------------------------------------------
-- Data for table `Students`
-- -----------------------------------------------------
START TRANSACTION;
INSERT INTO `Students` (`id`, `studentName`, `gender`, `birthDate`, `mNo`, `contactName`, `contactEmail`, `contactPhone`, `startDate`, `remarks`) VALUES ('s001', 'Sam Khew', 'm', '12/12/1991', 'nm', 'May Khew', '[email protected]', 0198829387, '12/07/2011', NULL);
INSERT INTO `Students` (`id`, `studentName`, `gender`, `birthDate`, `mNo`, `contactName`, `contactEmail`, `contactPhone`, `startDate`, `remarks`) VALUES ('s002', 'Joe Biden', 'm', '13/03/2003', 'nm', 'Layla Biden', '[email protected]', 0199283763, '14/05/2011', NULL);
INSERT INTO `Students` (`id`, `studentName`, `gender`, `birthDate`, `mNo`, `contactName`, `contactEmail`, `contactPhone`, `startDate`, `remarks`) VALUES ('s003', 'Bob Builder', 'm', '14/02/2002', 'LK920K', 'Mama Builder', '[email protected]', 0167728376, '29/02/2012', NULL);
INSERT INTO `Students` (`id`, `studentName`, `gender`, `birthDate`, `mNo`, `contactName`, `contactEmail`, `contactPhone`, `startDate`, `remarks`) VALUES ('s004', 'Kenny Koh', 'm', '18/02/1999', 'MM992', 'Lisa Koh', '[email protected]', 0123160231, '19/01/2012', NULL);
INSERT INTO `Students` (`id`, `studentName`, `gender`, `birthDate`, `mNo`, `contactName`, `contactEmail`, `contactPhone`, `startDate`, `remarks`) VALUES ('s005', 'Jane Doe', 'f', '29/09/1999', 'nm', 'Jackie Doe', '[email protected]', 0127736254, '02/03/2012', NULL);
INSERT INTO `Students` (`id`, `studentName`, `gender`, `birthDate`, `mNo`, `contactName`, `contactEmail`, `contactPhone`, `startDate`, `remarks`) VALUES ('s006', 'Lola Lai', 'f', '02/05/2004', 'nm', 'Mark Lai', '[email protected]', 0198827365, '11/09/2011', NULL);
COMMIT;
-- -----------------------------------------------------
-- Data for table `Schedule`
-- -----------------------------------------------------
START TRANSACTION;
INSERT INTO `Schedule` (`lesson_id`, `day`, `branch`, `level`, `time`, `ae`) VALUES ('sat1_s4', 'Sat', 'Sunway', 'basic', '4pm', 'Aini');
INSERT INTO `Schedule` (`lesson_id`, `day`, `branch`, `level`, `time`, `ae`) VALUES ('sat1_s5', 'Sat', 'Sunway', 'basic', '5pm', 'Aini');
INSERT INTO `Schedule` (`lesson_id`, `day`, `branch`, `level`, `time`, `ae`) VALUES ('sat1_s6', 'Sat', 'Sunway', 'basic', '6pm', 'Aini');
INSERT INTO `Schedule` (`lesson_id`, `day`, `branch`, `level`, `time`, `ae`) VALUES ('sat2_s4', 'Sat', 'Sunway', 'advance', '4pm', 'Nina');
INSERT INTO `Schedule` (`lesson_id`, `day`, `branch`, `level`, `time`, `ae`) VALUES ('sat2_s5', 'Sat', 'Sunway', 'advance', '5pm', 'Nina');
INSERT INTO `Schedule` (`lesson_id`, `day`, `branch`, `level`, `time`, `ae`) VALUES ('sat3_s6', 'Sat', 'Sunway', 'pre-comp', '6pm', 'Marcus');
COMMIT;
-- -----------------------------------------------------
-- Data for table `Link`
-- -----------------------------------------------------
START TRANSACTION;
INSERT INTO `Link` (`link_id`, `id`, `lesson_id`) VALUES ('L001', 's001', 'sat1_s4');
INSERT INTO `Link` (`link_id`, `id`, `lesson_id`) VALUES ('L002', 's002', 'sat1_s5');
INSERT INTO `Link` (`link_id`, `id`, `lesson_id`) VALUES ('L003', 's003', 'sat1_s6');
INSERT INTO `Link` (`link_id`, `id`, `lesson_id`) VALUES ('L004', 's004', 'sat2_s4');
INSERT INTO `Link` (`link_id`, `id`, `lesson_id`) VALUES ('L005', 's005', 'sat1_s5');
COMMIT;
-- -----------------------------------------------------
-- Data for table `Attendance`
-- -----------------------------------------------------
START TRANSACTION;
INSERT INTO `Attendance` (`date`, `attendance`, `link_id`) VALUES ('26/9/2012', '1', NULL);
COMMIT;
But then I get this error:
Executing SQL script in server
ERROR: Error 1005: Can't create table 'aquaticstar.link' (errno: 121)
I can't figure out why. Can anyone help me?
Solution 1:
I searched quickly for you, and it brought me here. I quote:
You will get this message if you're trying to add a constraint with a name that's already used somewhere else
To check constraints use the following SQL query:
SELECT
constraint_name,
table_name
FROM
information_schema.table_constraints
WHERE
constraint_type = 'FOREIGN KEY'
AND table_schema = DATABASE()
ORDER BY
constraint_name;
Look for more information there, or try to see where the error occurs. Looks like a problem with a foreign key to me.
Solution 2:
Foreign Key Constraint Names Have to be Unique Within a Database
Both @Dorvalla’s answer and this blog post mentioned above pointed me into the right direction to fix the problem for myself; quoting from the latter:
If the table you're trying to create includes a foreign key constraint, and you've provided your own name for that constraint, remember that it must be unique within the database.
I wasn’t aware of that. I have changed my foreign key constraint names according to the following schema which appears to be used by Ruby on Rails applications, too:
<TABLE_NAME>_<FOREIGN_KEY_COLUMN_NAME>_fk
For the OP’s table this would be Link_lession_id_fk, for example.