I have to come back to this one, because it itches me. Googling for this apparent shortcoming of MySQL I could not find any evidence for it. Also, the MySQL reference documentation mentions no historic or current limitation in the handling of composite foreign keys.
Here is a short interactive test of the support for composite foreign keys: mysql> create table master (id1 int, id2 int, primary key (id1, id2)) type=InnoDB; Query OK, 0 rows affected, 1 warning (0.02 sec) mysql> create table detail (id1 int, id2 int, constraint foreign key (id1, id2) references master (id1, id2) on delete cascade) type=InnoDB; Query OK, 0 rows affected, 1 warning (0.01 sec) mysql> insert into master values (1, 1), (2, 2); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> insert into detail values (3, 3); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`bla/detail`, CONSTRAINT `detail_ibfk_1` FOREIGN KEY (`id1`, `id2`) REFERENCES `master` (`id1`, `id2`) ON DELETE CASCADE) mysql> insert into detail values (1, 1), (2, 2); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> delete from master where id1 = 1 and id2 = 1; Query OK, 1 row affected (0.01 sec) mysql> select * from detail; +------+------+ | id1 | id2 | +------+------+ | 2 | 2 | +------+------+ 1 row in set (0.00 sec) The database correctly refuses an insert into the detail table if there is no corresponding entry in the master table. Also, the "on delete cascade" feature works as expected. The MySQL version I used is 5.0.51a. What else must be shown to work so that composite foreign key support may be enabled for MySQL in OpenJPA? -Martin. Martin Dirichs wrote: > > Yes, and there is no mention of this particular issue. > > -Martin. > > > Rick Curtis wrote: >> >> Here are the known MySQL issues -- >> http://openjpa.apache.org/builds/latest/docs/manual/manual.html#dbsupport_mysql_issues. >> >> -Rick >> >> >> Martin Dirichs wrote: >>> >>> Hi, >>> >>> wondering why OpenJPA's MappingTool refused to add a foreign key >>> constraint to the database (error message: openjpa.jdbc.Schema - The >>> foreign key "<foreignkey>" was not added to table "[...]"), I discovered >>> that composite foreign key support is explicitly disabled for MySQL. >>> These are the relevant code lines in >>> org.apache.openjpa.jdbc.sql.MySQLDictionary: >>> >>> protected String getForeignKeyConstraintSQL(ForeignKey fk) { >>> // mysql does not support composite foreign keys >>> if (fk.getColumns().length > 1) >>> return null; >>> return super.getForeignKeyConstraintSQL(fk); >>> } >>> >>> For all I know, composite foreign keys are possible with MySQL. At >>> least, a statement like >>> >>> ALTER TABLE detail ADD CONSTRAINT constraint_x FOREIGN KEY (masterId1, >>> masterId2) REFERENCES master (masterId1, masterId2); >>> >>> works perfectly well. Are there other deficits with MySQL foreign key >>> handling I am not aware of or are the above lines in >>> MySQLDictionary.java outdated / wrong? >>> >>> Regards, >>> Martin. >>> >>> >> >> > > -- View this message in context: http://n2.nabble.com/Composite-foreign-keys-with-MySQL-tp2772257p2792104.html Sent from the OpenJPA Users mailing list archive at Nabble.com.
