*
The moose likes Object Relational Mapping and the fly likes com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException" Watch "com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException" New topic
Author

com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException

Alejandro Barrero
Ranch Hand

Joined: Aug 01, 2005
Posts: 309
I am using Hibernate with MyEclipse and JPA. I am getting
com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException: Duplicate entry '8' for key 2
My source code is:

I created the table booking on MySQL as follows
create table booking(
id bigint not null auto_increment,
checkIn date,
checkOut date,
smoking bit,
creditCard varchar(10),
number varchar(16),
month varchar(2),
year varchar(4),
theUser bigint,
hotel bigint unique,
primary key (id),
foreign key (theUser) references theUser(id) ON DELETE CASCADE,
foreign key (hotel) references hotel(id) ON DELETE CASCADE)

The message is telling me that there is already a row with the second key (hotel) with value 8. I don't understand; I should be able to have several rows with the same foreign key.

Your help would be greatly appreciated,

Alejandro Barrero

Your help will be greatly appreciated,
Alejandro Barrero
Marco Ehrentreich
best scout
Bartender

Joined: Mar 07, 2007
Posts: 1280

Hi Alejandro,

what you should be able to have and what you really have may be two different things

It really depends how you model these things in your database. I you want to have a many-to-many association between bookings and hotels you will have to use a third table, let's call it booking_hotels which holds all desired pairs of hotel and booking keys.

Although you have only posted the create statement for the booking table the naming doesn't look like you have really modeled a many-to-many association in your database. Why not just annotate your classes correctly with @ManyToMany and let JPA create the tables for you? This way you could see the difference.

If you need more help it would be easier to see some more information...

Marco
Alejandro Barrero
Ranch Hand

Joined: Aug 01, 2005
Posts: 309
Thank you Marco. By your reply, it appears I am doing something wrong, but I don't see it. I want a one to many relation from table hotel to table booking, so I created the foreign key in table booking; my understanding is that any row in table booking with foreign key hotel = 8 is related to the row of table hotel with id = 8.
That is why I was expecting to be able to have several rows in booking with the same foreign key. Please explain what I am doing wrong.
Marco Ehrentreich
best scout
Bartender

Joined: Mar 07, 2007
Posts: 1280

Hi Alejandro,

if you want to have a 1:n relationship then basically everything is fine. You modeled this correctly by placing the foreign key of the "one"-side inside the table of your "many"-side.

I guess the only problem is the following line:
hotel bigint unique,

Why did you put a unique constraint on the column for the hotel id? The foreign key definition is OK but if you put a unique constraint here that means that every hotel id is only allowed once for the WHOLE TABLE. This is most probably not what you want because from your description I'm sure you would like to reuse the same hotel for different bookings

Just remove this "unique" keyword and give it another try...

Marco
Alejandro Barrero
Ranch Hand

Joined: Aug 01, 2005
Posts: 309
Of course, that makes it a one to one relation. Thank you very much.
Marco Ehrentreich
best scout
Bartender

Joined: Mar 07, 2007
Posts: 1280

You're welcome!

I know that it's sometimes hard to see the wood for the trees


Marco
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException
 
Similar Threads
Exception in Deleting Parent and Child entity
About foreign key
HQL query required with left outer join
Caused by: com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException
Could not create entity com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException: