I've tried to set key="foreign" after I created a relationship between two tables, a one to one relationship. And, it didn't work.
I checked the sql statement and I saw that comparison was empty (... AND t2.oid="" ...).
I didn't had time to look into it.
Can you give me an example? just to see that I did it correctly.
Dan.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Key="foreign" won't actually do anything. With the use of associations you don't actually need any foreign keys in your data base schema at all. You can still use them if you like, but they are not required (or used) by the framework.
Just make sure that your 1-1 association has an <entry /> tag for each field in the foreign key and you should be OK.
- Richard.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Anonymous
-
2004-06-15
Hi Richard,
I don't understand.
Can I create a relationship in my database or not?
Dan
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
1. Yes you can create a foreign key in your database.
2. Do you have to put something in the XML to indicate the foreign key exists? No.
3. Should you mark keys as foreign in the XML? No. It's not supported and probably won't be.
4. What happens if a foreign key rule is violated when inserting/updating data? An exception will be thrown by the save() method and the exception message will indicate what the problem is.
More Info: Not all databases support foreign keys (eg MySQL) so there is no default support for them. By creating associations in your XML you duplicate the functionality of foreign keys (without the contsraints) without actually needing them in the database and without having to worry about what order statements are executed in. Constraints can be built into your busienss classes and thereby become more intuitive for the programmer to maintain.
I hope that clears things up :-)
- Richard.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Anonymous
-
2004-06-16
Thanks.
Now its clear.
Dan
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Anonymous
-
2004-06-16
Hi Richard,
I've got a problem.
I have to objects one is an association of the other.
I've also created a one to one relationship in my db between the two tables.
I suceeded in persisting data in the db. But, when I tried to delete I got an exception that a unique constriant is vaiolated. I checked to see what happens, and I found that the first sql delete statement is done on the association object. But, in the db it is not possible to do so. It should delete the first object and only then it should delete the associated object.
Dan
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
This is what I was trying to indicate was possible in the previous post.
When you work with referential integrity (ie foreign key constraints) you need to perform SQL statements in a specific order to make sure that you will not violate the constraint checks.
The problem is that the framework executes SQL statements in an order based on the class structure and associations not on the constraints of foreign keys in the db schema.
Just out of curiosity, why do you have the foreign key constraints in the database? Do you allow updates to the db from outside of the application, or is there some other reason.
If it's a big problem I'll see what I can do to work around it, but I've not given it any real thought at this stage.
- Richard.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Anonymous
-
2004-06-17
Hi Richard,
The module that I'm writing is responsible to persist most of the application's object model.
We would like to be able to use standard report tools like, Bussiness Object or Crystal Report, to generate reports from the database.
I know that there is a way to still use standard report tools to query a db that does not have a schema. It'll just take longer to do that.
Dan.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I use crystal reports v9 against SQL server and Access for almost all of my reporting. I don't have any FK's in any of my databases anywhere and have had no problems at all.
Crystal is actually pretty good at guessing links between tables when you select multiple tables, and you can always change the links it generates.
Let me have a think about the issue and I'll see what I can come up with. It might be useful if you create a request to make sure I don't forget :-)
- Richard.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Hi Richard,
I've tried to set key="foreign" after I created a relationship between two tables, a one to one relationship. And, it didn't work.
I checked the sql statement and I saw that comparison was empty (... AND t2.oid="" ...).
I didn't had time to look into it.
Can you give me an example? just to see that I did it correctly.
Dan.
Hi Dan,
Key="foreign" won't actually do anything. With the use of associations you don't actually need any foreign keys in your data base schema at all. You can still use them if you like, but they are not required (or used) by the framework.
Just make sure that your 1-1 association has an <entry /> tag for each field in the foreign key and you should be OK.
- Richard.
Hi Richard,
I don't understand.
Can I create a relationship in my database or not?
Dan
I must have been a little unclear.
To summarize the rules for foreign keys....
1. Yes you can create a foreign key in your database.
2. Do you have to put something in the XML to indicate the foreign key exists? No.
3. Should you mark keys as foreign in the XML? No. It's not supported and probably won't be.
4. What happens if a foreign key rule is violated when inserting/updating data? An exception will be thrown by the save() method and the exception message will indicate what the problem is.
More Info: Not all databases support foreign keys (eg MySQL) so there is no default support for them. By creating associations in your XML you duplicate the functionality of foreign keys (without the contsraints) without actually needing them in the database and without having to worry about what order statements are executed in. Constraints can be built into your busienss classes and thereby become more intuitive for the programmer to maintain.
I hope that clears things up :-)
- Richard.
Thanks.
Now its clear.
Dan
Hi Richard,
I've got a problem.
I have to objects one is an association of the other.
I've also created a one to one relationship in my db between the two tables.
I suceeded in persisting data in the db. But, when I tried to delete I got an exception that a unique constriant is vaiolated. I checked to see what happens, and I found that the first sql delete statement is done on the association object. But, in the db it is not possible to do so. It should delete the first object and only then it should delete the associated object.
Dan
Hi Dan,
This is what I was trying to indicate was possible in the previous post.
When you work with referential integrity (ie foreign key constraints) you need to perform SQL statements in a specific order to make sure that you will not violate the constraint checks.
The problem is that the framework executes SQL statements in an order based on the class structure and associations not on the constraints of foreign keys in the db schema.
Just out of curiosity, why do you have the foreign key constraints in the database? Do you allow updates to the db from outside of the application, or is there some other reason.
If it's a big problem I'll see what I can do to work around it, but I've not given it any real thought at this stage.
- Richard.
Hi Richard,
The module that I'm writing is responsible to persist most of the application's object model.
We would like to be able to use standard report tools like, Bussiness Object or Crystal Report, to generate reports from the database.
I know that there is a way to still use standard report tools to query a db that does not have a schema. It'll just take longer to do that.
Dan.
I use crystal reports v9 against SQL server and Access for almost all of my reporting. I don't have any FK's in any of my databases anywhere and have had no problems at all.
Crystal is actually pretty good at guessing links between tables when you select multiple tables, and you can always change the links it generates.
Let me have a think about the issue and I'll see what I can come up with. It might be useful if you create a request to make sure I don't forget :-)
- Richard.