On Dec 21, 2009, at 2:58 PM, Ren, Kevin wrote:
Hi, Dave,
Thanks for your reply,
>Yes. This is the problem with MySQL not being able to initially defer the constraints. If you need to use MySQL, I suggest that you remove the Foreign Key constraint between STUDENT and PERSON in the database and trust that EOF will keep everything correct for you. In my case PERSON is abstract class not TABLE in DB, do you mean remove the Foreign Key constraint between Address and Home_address? Ah, yes. My mistake. The problem you are having has nothing to do with Vertical Inheritance, but this is a problem that Vertical Inheritance can have.
The FK constraint error you are getting:
Cannot add or update a child row: a foreign key constraint fails (`school`.`STUDENT`, CONSTRAINT `STUDENT_ibfk_1` FOREIGN KEY (`ADDRESS_ID`) REFERENCES `ADDRESS` (`ADDRESS_ID`))
is saying that it can't insert the student with an Address ID of 33 because there is no record in the ADDRESS table with a PK of 33. If the insert statement for the address were getting executed first, you would not get this error.
This is simply a problem with the lack of deferred constraints. The workaround is to remove the FK constraint in the DB in the Student Entity. No problem with that sequence, problem is try insert STUDENT first instead of ADDRESS.
If it's MYSQL issue, why it's working sometimes with correct generated SQL, like address, home_addres then student.
If EOF, WOnder or somebody else have some code to check this order of the insert table name has the right sequence, that would be working.
This is just the way EOF works. There is no guarantee that it will sequence the Database inserts in the same order every time. Personally, I do consider this a bug in EOF, but it is easily worked around by using deferred constraints in most databases. MSSQL Server and MySQL are two databases that don't support it.
Chuck Hill has written code that will force EOF to order insert and delete statements based on dependencies in the EOModel. I believe that this code is only in the MSSQL Server plugin. But even this fix isn't perfect becaise it doesn't handle situations where an entity has a relationship back to itself - it won't know which row to insert first. >I would also suggest that you avoid using Vertical Inheritance if you can. It is very inefficient when creating select statements in the database. It does make the database more normalized, but at the cost of efficiency. In this situation I would use Single Table Inheritance instead. I am just try to verify VI is working in EOF. Yes maybe need change to horizontal inheritance or Single Table Inheritance instead.
VI works, but it has problems that you need to work around. Lachlan Deck uses it a lot, and I used to, but I have found that eventually I always run into situations where it would have just been easier to use Single Table Inheritance instead.
The big exception to this is that if you are working with an existing database that you have no control over, you may end up needing to use Vertical Inheritance to properly model Inheritance. The only time I use VI now is if I need to support a legacy database. If I'm creating the database myself, I always use Single Table Inheritance.
Dave thanks
Xiaowen(Kevin) Ren
Analyst Programmer
Channel Applications, Retail Applications
ANZ National Bank Ltd
Level 6 ASB Tower
2 Hunter Street
PO Box 1791
Wellington
Phone: 04 4365363
Fax: 04 8028696
"This e-mail and any attachments to it (the
"Communication") is, unless otherwise stated, confidential, may contain
copyright material and is for the use only of the intended recipient. If you
receive the Communication in error, please notify the sender immediately by
return e-mail, delete the Communication and the return e-mail, and do not read,
copy, retransmit or otherwise deal with it. Any views expressed in the
Communication are those of the individual sender only, unless expressly stated
to be those of Australia and New Zealand Banking Group Limited ABN 11 005 357
522, or any of its related entities including ANZ National Bank Limited
(together "ANZ"). ANZ does not accept liability in connection with the integrity
of or errors in the Communication, computer virus, data corruption, interference
or delay arising from or in respect of the
Communication."
David Avendasora Senior Software Engineer K12, Inc.
***** ***** *****
|