Philip. you must have an index on the referenced key in the parent table. Now test_base has no indexes at all.
Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB Philip Molter wrote in message ... >On Fri, Jan 25, 2002 at 01:25:10PM -0600, Philip Molter wrote: >: I have a table with two fields that reference the same field in >: another table. Is this allowed (I'm not sure if it is). mysql >: 3.23.46 allows this, but apparently, mysql 3.23.47 does not. Create >: it with just one key and it's fine. Reference different tables >: and it's fine. >: >: mysql-3.23.47 InnoDB tables under Sparc Solaris 8 >: >: >: >: mysql> create table test_base ( fld int not null ); >: Query OK, 0 rows affected (0.07 sec) >: >: mysql> create table test_fk ( fld1 int not null, fld2 int not null, foreign key (fld1) references test_base(fld), foreign key (fld2 references test_base(fld) ); >: ERROR 1064: You have an error in your SQL syntax near 'references test_base(fld) )' at line 1 >: mysql> create table test_fk ( fld1 int not null, fld2 int not null, foreign key (fld1) references test_base(fld), foreign key (fld2) references test_base(fld) ); >: ERROR 1005: Can't create table './test/test_fk.frm' (errno: 150) >: mysql> > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
