[SOLVED] Foreign key problem - Errno 121

[codyH]

I am having trouble creating a db i am creating for my debate team. It is going into a rails server to input info about current event articles. When running mysql wow_development <db/create.sql i get the error 1005/errno121.

 

The order will have a magazine ID reference each magazine has one or more articles. If you can help i would greatly appreciate it.

 

cheers

c

 

the sql code is listed below.

 

-- phpMyAdmin SQL Dump

-- version 2.11.9.1

 

--

-- Host: localhost

-- Generation Time: Nov 26, 2008 at 10:00 AM

-- Server version: 5.0.67

-- PHP Version: 5.2.6

 

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

 

--

-- Database: `wow_development`

--

 

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

 

--

-- Table structure for table `articles`

--

 

CREATE TABLE IF NOT EXISTS `articles` (

`id` int(11) NOT NULL auto_increment,

`magazineID` int(11) NOT NULL,

`subjectOne` varchar(100) NOT NULL,

`subjectTwo` varchar(100) default NULL,

`subjectThree` varchar(100) default NULL,

`pageNumber` int(11) NOT NULL,

`foreignTopic` tinyint(1) NOT NULL,

`nationalTopic` tinyint(1) NOT NULL,

`warTopic` tinyint(1) NOT NULL,

`electionTopic` tinyint(1) NOT NULL,

`disasterTopic` tinyint(1) NOT NULL,

`economyTopic` tinyint(1) NOT NULL,

PRIMARY KEY (`id`),

KEY `magazineID_fk` (`magazineID`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

 

--

-- Dumping data for table `articles`

--

 

 

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

 

--

-- Table structure for table `magazines`

--

 

CREATE TABLE IF NOT EXISTS `magazines` (

`id` int(11) NOT NULL auto_increment,

`magTitle` set('US News','Newsweek','Economist','Time','Washington Post Weekly') NOT NULL,

`date` date default NULL,

`numArticles` int(11) NOT NULL default '0',

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

 

--

-- Dumping data for table `magazines`

--

 

 

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

 

--

-- Table structure for table `orders`

--

 

CREATE TABLE IF NOT EXISTS `orders` (

`id` int(11) NOT NULL auto_increment,

`user_id` int(11) NOT NULL,

`magazine_id` int(11) NOT NULL,

PRIMARY KEY (`id`),

KEY `fk_magazine_id` (`magazine_id`),

KEY `fk_user_id` (`user_id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

 

--

-- Dumping data for table `orders`

--

 

 

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

 

--

-- Table structure for table `users`

--

 

CREATE TABLE IF NOT EXISTS `users` (

`id` int(11) NOT NULL auto_increment,

`userName` varchar(20) NOT NULL,

`firstName` varchar(20) NOT NULL,

`lastName` varchar(20) NOT NULL,

`email` varchar(40) NOT NULL,

`password` varchar(20) default NULL,

`level` int(11) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

 

--

-- Dumping data for table `users`

--

 

 

--

-- Constraints for dumped tables

--

 

--

-- Constraints for table `articles`

--

ALTER TABLE `articles`

ADD CONSTRAINT `articles_ibfk_1` FOREIGN KEY (`magazineID`) REFERENCES `magazines` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

 

--

-- Constraints for table `orders`

--

ALTER TABLE `orders`

ADD CONSTRAINT `orders_ibfk_2` FOREIGN KEY (`magazine_id`) REFERENCES `magazines` (`id`) ON DELETE CASCADE,

ADD CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

[Mchl]

You sure it's foreign key problem? Error 1005 is 'Can't create table '%s' (errno: %d) '

 

Do you know which statement is causing the error?

[codyH]

When i ran the mysql statement it gave this as the error.  when i was looking online it seemed as though that it was like the foreign key problem.  I appreciate any help you can offer. 

[Mchl]

Try to run these statements one by one, and see which one gives the error.

Also try to catch the actual error message.

[RedRomeo]

I solved this error.

 

This happend because in MYSQL  you can't have two foreign keys with the same name. You need to change the value on your foreign key

 

Well this works for me.