I am using a EJB project to access my MySQL database. I keep getting an error, "more than one row returned" when I run a select * from budget table.
The error message states that the data provider returned more than one row when it should not. This normally will happen if the relationship defined in the entity beans does not correspond to the data that is stored in the underlying database. You may need to change the relationship or check your data.
I do not understand. Can you explain this to me ?
Assuming you have a "OneToOne" relationship between your Budget table and Budget Schedule table. (Example only).
The Budget Schedule table may be having more than one reference of the Budget in its data or vice versa.
OneToOne implies a row created in one table uniquely matches one row on the second table.
—————————————————————–
@Entity
class Budget {
@OneToOne
Private budgetSchedule = null
}
When the above code is deployed, a foreign key is created in the Budget Table. The relationship states that the Budget Schedule reference is unique and should not contain in more than one Budget row line.
In event 2 Budget rows are found holding the same reference to the Budget Schedule, you will get an error!
Thank you Stella!
I manually checked the rows of the table and yes, I can see that there are duplicate references in the data!
How could this happen if the relationship is defined as OneToOne in the first place ? Does not EJB apply referential integrity when rows are added ?
What I think that could have happened is that the programmer must have changed the relationship in the source code and had forgotten to clean up the data.
When you modify the relationship in the source code AFTER the table has got created, EJB will only apply the rules for NEW data and not for existing data.
That must be the error you are experiencing now when you try to retrieve data that was added when the constraint was missing or was previously defined as OneToMany.
How can I make sure of what you say ? Is there a method to verify this behavior ?
What I could suggest is that you drop the tables and deploy your EJB project. Then verify by adding new data.
Or you can delete the old records from the last duplicated entry.
Don't forget to backup your database before this!
You should not be getting this problem again!
Ouch! Dropping is a drastic action 🙂
I will try the delete option and let you know the outcome!
Hi Stella, just to give you an update. I deleted the old data and tried a few rounds. It now works perfectly without any errors.
I checked the data and all rows are unique as well.
Thank you for helping me out !
Glad to be of Help! Have a nice day!
