MySQL ODBC Connector - Foreign Key Help

Author

Foreign Key Help

Jesse

2005-12-22, 8:24 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

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

SGreen@unimin.com

2005-12-22, 8:24 pm

--=_alternative 006DDB10852570DF_=
Content-Type: text/plain; charset="US-ASCII"

"Jesse" <jlc@msdlg.com> 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

--=_alternative 006DDB10852570DF_=--

Jesse

2005-12-22, 8:24 pm

------ =_NextPart_000_0011_
01C60709.F76DB4E0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

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 -----=20
From: SGreen@unimin.com=20
To: Jesse=20
Cc: mysql@lists.mysql.com=20
Sent: Thursday, December 22, 2005 3:00 PM
Subject: Re: Foreign Key Help

"Jesse" <jlc@msdlg.com> wrote on 12/22/2005 02:51:21 PM:

> I am trying to add referential integrity to my database. I'm trying =
to add=20
> a foreign key reference to one of my tables, but I'm getting an =
error when I=20
> try to do so. Here's what I'm executing:
>=20
> ALTER TABLE Campers ADD CONSTRAINT FK_Activities FOREIGN KEY =
FK_Activities=20[col
or=darkred]
> (ID)
> REFERENCES ActivitySelections (PersonID)
> ON DELETE CASCADE
> ON UPDATE CASCADE;
>=20
> When I execute this in the command line utility, I get the error, =[/color]
" Can't=20
> create table '.\fccamp\#sql-33c_30.frm' (errno: 150)"
>=20
> Does anyone know what this means, and how I can fix it? I'm using =
MySQL=20
> 5.?? on a Windows XP Pro (development machine).
>=20
> Thanks,
> Jesse=20
>=20

the "Simplified Rules" for creating foreign keys:=20

a) both tables must be InnoDB=20
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.=20
c) there can be no data already in the child table that would =
otherwise violate the key you are trying to create.=20

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.=20

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine=20

------ =_NextPart_000_0011_
01C60709.F76DB4E0--

Sponsored Links