[RESOLVED] mysql error 1005 errno 121 when trying to set a foreign key

[s0me0ne]

I'm trying to setup a table that has 2 foreign keys. I was able to set 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

[B]SITES[/B]
+-------------+---------------------+------+-----+---------------------+----------------+
| 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 |                     |        |
+-------------+---------------------+------+-----+---------------------+----------------+

[B]HOSTS[/B]
+---------+---------------------+------+-----+-------------------+----------------+
| Field   | Type                | Null | Key | Default           | Extra  |
+---------+---------------------+------+-----+-------------------+----------------+
| id      | int(10) unsigned    | NO   | PRI | NULL              | auto_increment |
| title   | varchar(100)        | NO   |     |                   |  |
| status  | tinyint(3) unsigned | NO   |     |                   |  |
| pubdate | timestamp           | NO   |     | CURRENT_TIMESTAMP |  |
+---------+---------------------+------+-----+-------------------+----------------+

[B]SITES_HOSTS[/B]
+---------+------------------+------+-----+---------+----------------+
| Field   | Type             | Null | Key | Default | Extra          |
+---------+------------------+------+-----+---------+----------------+
| id      | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| site_id | int(10) unsigned | NO   |     |         |                |
| host_id | int(10) unsigned | NO   | MUL |         |                |
+---------+------------------+------+-----+---------+----------------+

[NogDog]

All I can see from the error codes list is that 1005 is a "Can't create table" error, which I'm not sure how that applies here. Could it be a permissions issue for the database user being used for this operation?

[s0me0ne]

It's not a permissions issue, because I can modify the table or database just fine. It just wont let me set this foriegn key.

[igorlopez]

Even if you are not good at the command line it will not hurt to get into the sql server.
At the terminal just enter: mysql --user=USERNAME --password=PASSWORD
replace USERNAME and PASSWORD with yours.
At the mysql prompt, mysql>
enter the command: mysql>use MYDB
where MYDB is the database you will work on
Once the databse is selected you can start work directly and in this case just copy and paste the command you gave except the db_special. part, i.e:
ALTER TABLE sites_hosts ADD CONSTRAINT site_id FOREIGN KEY site_id (site_id)
REFERENCES sites (id)
ON DELETE RESTRICT
ON UPDATE RESTRICT;
You will now get a clearer answer on what the problem is.

Add your table constructs so that we can test on our servers.
(We could replicate the tables from your info but it is just too tedious)

[s0me0ne]

I tried it from the command line and got this error

ERROR 1005 (HY000): Can't create table '.\db_special#sql-224_157.frm' (errno: 121)

I did a mysql dump so you could create the tables and try it yourself.

-- phpMyAdmin SQL Dump
-- version 2.10.3
-- http://www.phpmyadmin.net
-- 
-- Host: localhost
-- Generation Time: Sep 28, 2008 at 04:02 PM
-- Server version: 5.0.45
-- PHP Version: 5.2.3

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

-- 
-- Database: `db_special`
-- 

-- --------------------------------------------------------

-- 
-- Table structure for table `sites`
-- 

DROP TABLE IF EXISTS `sites`;
CREATE TABLE IF NOT EXISTS `sites` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `title` varchar(100) NOT NULL,
  `description` longtext,
  `url` varchar(150) default NULL,
  `status` tinyint(3) unsigned NOT NULL,
  `pubdate` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `updated` timestamp NOT NULL default '0000-00-00 00:00:00',
  `subtitle` varchar(100) default NULL,
  `sitetype_id` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `sitetype_id` (`sitetype_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

-- 
-- Dumping data for table `sites`
-- 


-- --------------------------------------------------------

-- 
-- Table structure for table `sites_hosts`
-- 

DROP TABLE IF EXISTS `sites_hosts`;
CREATE TABLE IF NOT EXISTS `sites_hosts` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `site_id` int(10) unsigned NOT NULL,
  `host_id` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `host_id` (`host_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

-- 
-- Dumping data for table `sites_hosts`
-- 


-- --------------------------------------------------------

-- 
-- Table structure for table `hosts`
-- 

DROP TABLE IF EXISTS `hosts`;
CREATE TABLE IF NOT EXISTS `hosts` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `title` varchar(100) NOT NULL,
  `status` tinyint(3) unsigned NOT NULL,
  `pubdate` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=16 ;

-- 
-- Constraints for dumped tables
-- 

-- 
-- Constraints for table `sites`
-- 
ALTER TABLE `sites`
  ADD CONSTRAINT `sitetype_id` FOREIGN KEY (`sitetype_id`) REFERENCES `sitetypes` (`id`);

-- 
-- Constraints for table `sites_hosts`
-- 
ALTER TABLE `sites_hosts`
  ADD CONSTRAINT `host_id` FOREIGN KEY (`host_id`) REFERENCES `hosts` (`id`);

[igorlopez]

OK, I copied your tables and tried the alter command and got exactly the same eror.

This question is more for someone like laserlight who is a king on sql.

Sorry for not being able to help.

[Weedpacket]

I remember this error. Yay for informative error messages. MySQL alters tables by creating a new temporary table that copies the old table and includes the alteration, then renames it. If a foreign key constraint fails while creating the temporary table, that's when you get the error.

Apparently (according to 13.2.6.4) you need to create all the necessary indexes (on site_id, in this case) yourself before adding the constraint; MySQL won't create one automatically.

[s0me0ne]

I used the following and it worked...

DROP TABLE IF EXISTS `sites_hosts`;
CREATE TABLE IF NOT EXISTS `sites_hosts` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `site_id` int(10) unsigned NOT NULL,
  `host_id` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `site_id` (`site_id`),
  KEY `host_id` (`host_id`),
  CONSTRAINT FOREIGN KEY (`site_id`) REFERENCES `sites` (`id`),
  CONSTRAINT FOREIGN KEY (`host_id`) REFERENCES `hosts` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

However I'm confused as to why it named the Foreign Keys with a key name of
sites_hosts_ibfk_1
sites_hosts_ibfk_2

😕

Here is a screenshot from the MySQL Query Browser

[Weedpacket]

It has to name them something.... "sites_hosts" 'cos that's the name of the table, "ib" is probably InnoBase, "fk" for "foreign key" and the rest you can figure out for yourself.