Martin, I suspect the MySQLDictionary is outdated.
Could you please create a JIRA [1] issue for this? In the meantime, you can create a custom dictionary, override the mentioned method and see what happens. Greetings, Milosz [1] http://issues.apache.org/jira/browse/OPENJPA > 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 "" 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. > >
