One other restriction that I found was that both columns must be of the same type. That was my problem. I had one column set to INTEGER and the other set to INT(10). I set the INT(10) to INTEGER, and it worked fine.
Thanks, Jesse ----- Original Message ----- From: [EMAIL PROTECTED] To: Jesse Cc: mysql@lists.mysql.com Sent: Thursday, December 22, 2005 3:00 PM Subject: Re: Foreign Key Help "Jesse" <[EMAIL PROTECTED]> wrote on 12/22/2005 02:51:21 PM: > I am trying to add referential integrity to my database. I'm trying to add > a foreign key reference to one of my tables, but I'm getting an error when I > try to do so. Here's what I'm executing: > > ALTER TABLE Campers ADD CONSTRAINT FK_Activities FOREIGN KEY FK_Activities > (ID) > REFERENCES ActivitySelections (PersonID) > ON DELETE CASCADE > ON UPDATE CASCADE; > > When I execute this in the command line utility, I get the error, "Can't > create table '.\fccamp\#sql-33c_30.frm' (errno: 150)" > > Does anyone know what this means, and how I can fix it? I'm using MySQL > 5.?? on a Windows XP Pro (development machine). > > Thanks, > Jesse > the "Simplified Rules" for creating foreign keys: a) both tables must be InnoDB b) all columns involved (in both parent and child tables) must be the leftmost portion of at least one index. It's preferable if the parent column(s) is/are part of a PK or UNIQUE index. c) there can be no data already in the child table that would otherwise violate the key you are trying to create. To see more details about this error (or any other problem going on in InnoDB), run the command SHOW InnoDb STATUS. There will be about 40 or 50 lines of output so if it scrolls off of the screen and your screen buffer isn't big enough, you will not be able to see the details of the error because what you want to look at is near the top of the report. Resize your buffer and try again. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
