Ed Smith <[EMAIL PROTECTED]> wrote: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?
2. Change type of enroll.sid to VARCHAR(5).
3. Change "Earl" to "Early"
My theory: student.sid get "silently changed" from CHAR to VARCHAR since student.name is VARCHAR; however, enroll.sid does not change because there are no variable length fields in enroll. This means that
enroll.sid = "Earl " student.sid = "Earl"
Consequently, there is no match.
Note that I did try specifying a length for the index on sid (i.e., "INDEX sidindex (sid(5)),") but that didn't help. Even shortening to 4 doesn't help, which doesn't jive with my cohersion theory, assuming I understand the index length specification.
I tested your example and it worked fine for me. I didn't get any error. What is value of default-character-set?
I tried it and it worked for me as well -- with MySQL 4.1.1. With 4.1.0, I get the same error. Looks like a problem with 4.1.0 that is fixed in 4.1.1.
-- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com
Are you MySQL certified? http://www.mysql.com/certification/
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
