MySQL Forums
Forum List  »  InnoDB

another fool having problems with FK in InnoDB
Posted by: Mat private
Date: April 03, 2009 02:57AM

Hi,

I've been testing FK in innodb and was able to create a working test on some tables. But... on these two tables (just an example) I keep on getting the famous Error code: 1005.

These are the schema's:

CREATE TABLE `table1` (
`Table1_id` int(11) unsigned NOT NULL auto_increment,
`name` varchar(20) collate latin1_general_ci NOT NULL,
PRIMARY KEY (`Table1_id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;


CREATE TABLE `Table2` (
`Table2_id` int(11) unsigned NOT NULL auto_increment,
`name_id` int(11) unsigned NOT NULL,
`other_info_id` int(11) NOT NULL,
`some_more` varchar(10) collate latin1_general_ci NOT NULL,
PRIMARY KEY (`Table2_id`),
KEY `name_id` (`name_id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;


I need to be able to make a foreign key on table 2 after it was created (I was just testing if I could, becasue for an already existing database, I need to alter the tables, to be able to use FK).

So, after creating the test tables, I ran this query:

alter table testinnodb.table2
add foreign key (name_id)
references table1(Table1_id)
on delete set null
on update cascade;

This is where I get the error. I don't understand why. The datatypes are the same, they both have an index for the columns needed...I just don't get it...
Can you help me out?
Kind regards,

Matthijs

Options: ReplyQuote


Subject
Views
Written By
Posted
another fool having problems with FK in InnoDB
2865
April 03, 2009 02:57AM


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.