Ed, I was able to repeat the error in MySQL-4.1.0 on XP, with the default latin1 charset. But 4.0.15a works ok. The bug fixes of 4.0.xx will be in 4.1.1, which will be out around Nov 15th.
Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for MySQL ............................. C:\temp\mysql-4.0.15-win-noinstall\mysql-4.0.15\bin>mysqld-max --standalone --co nsole --default-table-type=innodb InnoDB: The first specified data file C:\mysql\ibdata1 did not exist: InnoDB: a new database to be created! 030930 1:15:24 InnoDB: Setting file C:\mysql\ibdata1 size to 64 MB InnoDB: Database physically writes the file full: wait... 030930 1:15:26 InnoDB: Log file C:\mysql\ib_logfile0 did not exist: new to be created InnoDB: Setting log file C:\mysql\ib_logfile0 size to 10 MB InnoDB: Database physically writes the file full: wait... 030930 1:15:27 InnoDB: Log file C:\mysql\ib_logfile1 did not exist: new to be created InnoDB: Setting log file C:\mysql\ib_logfile1 size to 10 MB InnoDB: Database physically writes the file full: wait... InnoDB: Doublewrite buffer not found: creating new InnoDB: Doublewrite buffer created InnoDB: Creating foreign key constraint system tables InnoDB: Foreign key constraint system tables created 030930 1:15:33 InnoDB: Started mysqld-max: ready for connections. Version: '4.0.15-max' socket: '' port: 3306 C:\temp\mysql-4.0.15-win-noinstall\mysql-4.0.15\bin>mysql test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 4.0.15-max Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> CREATE TABLE student ( -> sid CHAR(5) PRIMARY KEY, -> name VARCHAR(30) -> ); Query OK, 0 rows affected (0.03 sec) mysql> mysql> CREATE TABLE course ( -> cid CHAR(5) PRIMARY KEY, -> name CHAR(20) -> ); Query OK, 0 rows affected (0.03 sec) mysql> mysql> CREATE TABLE enroll ( -> cid CHAR(5) NOT NULL, -> sid CHAR(5) NOT NULL, -> PRIMARY KEY(cid, sid), -> INDEX sidindex (sid), -> FOREIGN KEY (sid) REFERENCES student(sid), -> FOREIGN KEY (cid) REFERENCES course(cid) -> ); Query OK, 0 rows affected (0.05 sec) mysql> mysql> INSERT INTO student VALUES ('Earl', 'Earl Jones'); Query OK, 1 row affected (0.03 sec) mysql> INSERT INTO course VALUES ('BRTLT', 'British '> Literature'); Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO enroll VALUES ('BRTLT', 'Earl'); Query OK, 1 row affected (0.03 sec) ----- Original Message ----- From: "Ed Smith" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Tuesday, September 30, 2003 12:09 AM Subject: Re: FOREIGN KEY Weirdness in mySQL 4.1 with VARCHAR > I installed a fresh copy mySQL-4.1alpha on XP. I ran > it standalone as follows: > > mysqld-max --standalone --console > --default-table-type=innodb > > I get the same results with my script. > > Since I didn't specify a character set, I assume it is > the default (ISO-8859-1) set as specified in the > Section 4.6.1 of the 4.1 manual. I appreciate any and > all help. For now, I am having to disable foreign key > integrity constraint checking. > > Ed > > --- Victoria Reznichenko > <[EMAIL PROTECTED]> wrote: > > 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? > > > > > > -- > > For technical support contracts, goto > > https://order.mysql.com/?ref=ensita > > This email is sponsored by Ensita.net > > http://www.ensita.net/ > > __ ___ ___ ____ __ > > / |/ /_ __/ __/ __ \/ / Victoria Reznichenko > > / /|_/ / // /\ \/ /_/ / /__ > > [EMAIL PROTECTED] > > /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net > > <___/ www.mysql.com > > > > > > > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: > > > http://lists.mysql.com/[EMAIL PROTECTED] > > > > > __________________________________ > Do you Yahoo!? > The New Yahoo! Shopping - with improved product search > http://shopping.yahoo.com > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
