[Texo] Database foreign key constraints [message #1305931] |
Sun, 20 April 2014 18:37 |
Eclipse User |
|
|
|
Hello,
I work on an academic project where we plan to use Texo for storing and
accessing ecore defined data via a rest interface.
The required functionality is very similar to what the web.example
project
(https://github.com/mtaal/texo/tree/master/org.eclipse.emf.texo.web.example)
does.
Unfortunately, after generating mysql-persisted test-data we encounter
an exception when trying to delete an instance (e.g. a book) via a
jsonws call.
"Internal Exception: java.sql.SQLIntegrityConstraintViolationException:
DELETE on table 'LIBRARY_BOOK' caused a violation of foreign key constraint"
Our current workaround is to tell Eclipselink to write the sql-scheme to
a file instead of directly applying it on the database. Then we append a
“ON DELETE CASCADE” to each foreign key constraint and apply the altered
scheme on the database.
Is there a way to achieve this behavior without needing a workaround?
I would be very glad if you could help me.
With Best Regards,
Jan Moritz
|
|
|
Re: [Texo] Database foreign key constraints [message #1306115 is a reply to message #1305931] |
Sun, 20 April 2014 21:06 |
Martin Taal Messages: 5468 Registered: July 2009 |
Senior Member |
|
|
Hi Moritz,
Yes there is a reference from the library to a book. So from one point of view you should first remove the book from the
books collection in the library. See also this wiki link:
https://wiki.eclipse.org/Texo/ObjectStore#Cross_Reference_Queries
Having said that, automatically removing references can make sense ofcourse, it depends on the business case you try to
model. But in this case with the solution you do, you could get 'holes' in the books collection. As you remove the
element from the books collection without updating the books collection itself.
Removing the join table record on delete of an element corresponds to the on-delete-null db, afaics this is however not
supported by Hibernate, I think the same applies to EclipseLink:
http://stackoverflow.com/questions/9944137/have-jpa-hibernate-to-replicate-the-on-delete-set-null-functionality
http://stackoverflow.com/questions/8243400/on-delete-set-null-in-hibernate-in-onetomany
So I think the best approach is to clean the references to the object before deleting. You can implement this clean up
code on the server side using the DAO concept, the webservice layer makes use of generated/customized DAO implementations:
https://wiki.eclipse.org/Texo/Dao
gr. Martin
On 20-04-14 20:37, jahoefne wrote:
> Hello,
>
> I work on an academic project where we plan to use Texo for storing and accessing ecore defined data via a rest interface.
> The required functionality is very similar to what the web.example project
> (https://github.com/mtaal/texo/tree/master/org.eclipse.emf.texo.web.example) does.
>
> Unfortunately, after generating mysql-persisted test-data we encounter an exception when trying to delete an instance
> (e.g. a book) via a jsonws call.
> "Internal Exception: java.sql.SQLIntegrityConstraintViolationException: DELETE on table 'LIBRARY_BOOK' caused a
> violation of foreign key constraint"
>
> Our current workaround is to tell Eclipselink to write the sql-scheme to a file instead of directly applying it on the
> database. Then we append a “ON DELETE CASCADE” to each foreign key constraint and apply the altered scheme on the database.
>
> Is there a way to achieve this behavior without needing a workaround?
>
> I would be very glad if you could help me.
>
> With Best Regards,
> Jan Moritz
--
With Regards, Martin Taal
Springsite/Elver.org
Office: Hardwareweg 4, 3821 BV Amersfoort
Postal: Nassaulaan 7, 3941 EC Doorn
The Netherlands
Cell: +31 (0)6 288 48 943
Tel: +31 (0)84 420 2397
Fax: +31 (0)84 225 9307
Mail: mtaal@xxxxxxxx - mtaal@xxxxxxxx
Web: www.springsite.com - www.elver.org
|
|
|
Powered by
FUDForum. Page generated in 0.03660 seconds