Hi, ----- Original Message ----- From: "Roman Neuhauser" Sent: Friday, September 26, 2003 6:05 PM Subject: Re: FOREIGN KEY Weirdness in mySQL 4.1 with VARCHAR
> # [EMAIL PROTECTED] / 2003-09-26 15:12:03 -0700: > > --- Harald Fuchs <[EMAIL PROTECTED]> wrote: > > > Ed Smith <[EMAIL PROTECTED]> writes: > > > > Greetings. When I execute the SQL script below in > > > > mySQL 4.1, I get > > > > > > > ERROR 1216: Cannot add or update a child row: a foreign key > > > > constraint fails > > > > > > > It is, of course, choking on the enroll row insert. Why is this > > > > happening? Here are some things that make the problem go away: > > > > > > > 1. Take out the "name VARCHAR(30)" attribute from student OR > > > > change the type to CHAR(30): In Section 6.5.3.1 (second bullet), > > > > it says that if one attribute is variable length, all attributes > > > > "silently" become variable length. Does this mean student.sid is > > > > really a VARCHAR? Could this be related to the problem? > > > > > > Exactly. The VARCHAR name switches sid from CHAR to VARCHAR (as > > > SHOW CREATE TABLE would have told you), whereas enroll.sid stays > > > CHAR because there's no VARCHAR column. > > > What's the best work around for this "feature"? It > > looks like I will be forced to change my enroll to use > > VARCHAR. I shouldn't have to do this to make it work. > > Other suggestions? > > Why do you use VARCHAR in the other table in the first place? > Fixed record length is always better. Indeed, but you can't get a CHAR(>3) even when you want it if you have other fields that need to be TEXT/BLOB. MySQL, in its infinite wisdom, and which I'm getting very tired of, thinks that your CHAR fields will be better off as VARCHARs. :mad: When *I KNOW* what length a field's content *WILL ALWAYS BE*, and *I SPECIFY* CHAR, I don't care what MySQL thinks because of other column types. I WANT IT CHAR!!! :-) Well, now you know what I think... Destroy the silent CHAR -> VARCHAR change please! (VARCHAR(<3) -> CHAR is OK.) Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
