Mysql Error 1005 Foreign Key Errno 121
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up SQL - error code 1005 with error number 121 up vote 12 down vote favorite 4 I'm running the following MySQL script (trimmed down), generated automatically by MySQL Workbench and I get the following error: Error Code: 1005 Can't create table 'regula.reservation' (errno: 121) I'm not very proficient with databases and this error is not very informative. What is the problem here? -- ----------------------------------------------------- -- Table `regula`.`Users` -- ----------------------------------------------------- DROP TABLE IF EXISTS `regula`.`Users` ; CREATE TABLE IF NOT EXISTS `regula`.`Users` ( `idUsers` INT UNSIGNED NOT NULL AUTO_INCREMENT , `name` TEXT NOT NULL , `type` TEXT NOT NULL , `pwd` TEXT NOT NULL , PRIMARY KEY (`idUsers`) , UNIQUE INDEX `idUsers_UNIQUE` (`idUsers` ASC) ) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `regula`.`Projects` -- ----------------------------------------------------- DROP TABLE IF EXISTS `regula`.`Projects` ; CREATE TABLE IF NOT EXISTS `regula`.`Projects` ( `idProjects` INT UNSIGNED NOT NULL AUTO_INCREMENT , `ownerId` INT UNSIGNED NOT NULL , `name` TEXT NOT NULL , `date` DATE NOT NULL , `time` TIME NOT NULL , `place` TEXT NOT NULL , `itemType` INT NOT NULL , PRIMARY KEY (`idProjects`) , UNIQUE INDEX `idProjects_UNIQUE` (`idProjects` ASC) , INDEX `ownerId` (`ownerId` ASC) , CONSTRAINT `ownerId` FOREIGN KEY (`ownerId` ) REFERENCES `regula`.`Users` (`idUsers` ) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `regula`.`ItemTypes` -- ----------------------------------------------------- DROP TABLE IF EXISTS `regula`.`ItemTypes` ; CREATE TABLE I
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up MySQL “ERROR 1005 (HY000): Can't create table 'foo.#sql-12c_4' http://stackoverflow.com/questions/4412693/sql-error-code-1005-with-error-number-121 (errno: 150)” up vote 38 down vote favorite 5 I was working on creating some tables in database foo, but every time I end up with errno 150 regarding the foreign key. Firstly, here's my code for creating tables: CREATE TABLE Clients ( client_id CHAR(10) NOT NULL , client_name CHAR(50) NOT NULL , provisional_license_num CHAR(50) NOT NULL , client_address CHAR(50) NULL , client_city CHAR(50) NULL , client_county http://stackoverflow.com/questions/2799021/mysql-error-1005-hy000-cant-create-table-foo-sql-12c-4-errno-150 CHAR(50) NULL , client_zip CHAR(10) NULL , client_phone INT NULL , client_email CHAR(255) NULL , client_dob DATETIME NULL , test_attempts INT NULL ); CREATE TABLE Applications ( application_id CHAR(10) NOT NULL , office_id INT NOT NULL , client_id CHAR(10) NOT NULL , instructor_id CHAR(10) NOT NULL , car_id CHAR(10) NOT NULL , application_date DATETIME NULL ); CREATE TABLE Instructors ( instructor_id CHAR(10) NOT NULL , office_id INT NOT NULL , instructor_name CHAR(50) NOT NULL , instructor_address CHAR(50) NULL , instructor_city CHAR(50) NULL , instructor_county CHAR(50) NULL , instructor_zip CHAR(10) NULL , instructor_phone INT NULL , instructor_email CHAR(255) NULL , instructor_dob DATETIME NULL , lessons_given INT NULL ); CREATE TABLE Cars ( car_id CHAR(10) NOT NULL , office_id INT NOT NULL , engine_serial_num CHAR(10) NULL , registration_num CHAR(10) NULL , car_make CHAR(50) NULL , car_model CHAR(50) NULL ); CREATE TABLE Offices ( office_id INT NOT NULL , office_address CHAR(50) NULL , office_city CHAR(50) NULL , office_County CHAR(50) NULL , office_zip CHAR(10) NULL , office_phone INT NULL , office_email CHAR(255) NULL ); CREATE TABLE Lessons ( lesson_num INT NOT NULL , client_id CHAR(10) NOT NULL , date DATETIME NOT NULL , time DATETIME NOT NULL , milegage_used DECIMAL(5, 2) NULL ,
set a foreign key If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have http://board.phpbuilder.com/showthread.php?10359413-RESOLVED-mysql-error-1005-errno-121-when-trying-to-set-a-foreign-key to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. Results 1 http://dba.stackexchange.com/questions/33795/error-error-1005-cant-create-table-progetto-museo-errno-121 to 9 of 9 Thread: [RESOLVED] mysql error 1005 errno 121 when trying to set a foreign key Tweet Thread Tools Show Printable Version Email this Page… Subscribe to this Thread… mysql error Search Thread Advanced Search Display Linear Mode Switch to Hybrid Mode Switch to Threaded Mode 09-27-2008,01:32 PM #1 s0me0ne View Profile View Forum Posts Senior Member Join Date May 2003 Posts 121 [RESOLVED] mysql error 1005 errno 121 when trying to set a foreign key I'm trying to setup a table that has 2 foreign keys. I was able to set mysql error 1005 host_id as a Foreign Key, but I cannot get site_id to be a foreign key in the table SITES_HOSTS. I get mysql error 1005 errno 121 I'm using the MySQL Query Browser when I'm doing this, because I'm not good enough to use the command line, but the app shows me its doing this ALTER TABLE `db_special`.`sites_hosts` ADD CONSTRAINT `site_id` FOREIGN KEY `site_id` (`site_id`) REFERENCES `sites` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT; Here are my tables Code: SITES +-------------+---------------------+------+-----+---------------------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | title | varchar(100) | NO | | | | | description | longtext | YES | | NULL | | | url | varchar(150) | YES | | NULL | | | status | tinyint(3) unsigned | NO | | | | | pubdate | timestamp | NO | | CURRENT_TIMESTAMP | | | updated | timestamp | NO | | 0000-00-00 00:00:00 | | | subtitle | varchar(100) | YES | | NULL | | | sitetype_id | int(10) unsigned | NO | MUL | | | +-------------+---------------------+------+-----+-----------
log in tour help Tour Start here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Database Administrators Questions Tags Users Badges Unanswered Ask Question _ Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute: Sign up Here's how it works: Anybody can ask a question Anybody can answer The best answers are voted up and rise to the top ERROR: Error 1005: Can't create table 'progetto.museo' (errno: 121) up vote 1 down vote favorite I've found on the web that: You will get this message if you're trying to add a constraint with a name that's already used somewhere else and 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. Run this query to see if that name is in use somewhere BUT if you can see my script, many tables are created with the same name's INDEX... so what have i to change? (i've posted the first lines of the script to create the database, the file is much bigger) 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'; CREATE SCHEMA IF NOT EXISTS `PROGETTO` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ; USE `PROGETTO` ; -- ----------------------------------------------------- -- Table `PROGETTO`.`SETTORE` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `PROGETTO`.`SETTORE` ( `Comune` CHAR NOT NULL , `superficie` INT(11) NULL , PRIMARY KEY (`Comune`) ) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `PROGETTO`.`PAESE` -- -