You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@openjpa.apache.org by Martin Dirichs <na...@dirichs.fastmail.fm> on 2009/05/02 22:06:04 UTC
Composite foreign keys with MySQL
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-tp2772257p2772257.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.
Re: Composite foreign keys with MySQL
Posted by Miłosz Tylenda <mt...@o2.pl>.
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.
>
>
Re: Composite foreign keys with MySQL
Posted by Martin Dirichs <na...@dirichs.fastmail.fm>.
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.
Re: Composite foreign keys with MySQL
Posted by Martin Dirichs <na...@dirichs.fastmail.fm>.
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-tp2772257p2777383.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.
Re: Composite foreign keys with MySQL
Posted by Rick Curtis <cu...@gmail.com>.
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-tp2772257p2772619.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.