I have a database schema issue, and I would like to have some opinions.
Imagine a object car. In the first version of the software I develop, my customers requested my to manage 4 wheels on each car: so I had a collection of wheels in my class car. 3 tables were created:
In the second version of the software, my customers informed my that they did not need 4 wheels anymore, but only one per car. No problem for me, I just had to remove the wheel collection from my class car, and instead put a attribute wheel.
2 tables are needed in that case
So when I update the database schema with the VSChema utility, everything works find, except that the table car_wheel remains in the database.
When I want to remove a car from the database, I first remove the associated wheel, and when I want to remove the car itself, an error is raised, telling that some reference of the car still exist in the car_wheel table, and that the removal of the car is forbidden.
Am I missing something in the schema update process, or do I have a possibility to remove the unused table car_wheel?
If not I have to remove all entries from the table manually before trying to remove my car?
7 Answers, 1 is accepted
do you think there is a way to have a workaround concerning this problem, as it is a blocking point for me.
I use the VSchema to update the database, and all my customers have not the same version of the software, and thus the same database schema.
I want to let VSchema manage the database updates.
Thanks for your response,
There is an still artifact of your first schema creation steps in the database. Because the join table generated by your first schema generation has a foreign key reference to car it keeps your data inserted into the first schema in a no more deletable state.
The only way is to drop the join table with all constraints.
There are several ways to do that. My prefered way is not to ship vschema to the customer but let vschema generate the dll script that can be applied to the customer's database during update.
If you once have the ddl script it is easy to add the drop statements.
To do so vschema has the -direct option. If that is set it to -direct- we are only generating the script. If you do not want to apply the dll script inside the installer you can still use vschema and let it execute your ddl script only.
the Telerik team
However, I really don't want to change the way the update of database schema is done. I find very convenient the fact that VSChema makes all the job alone, without having to generate and update the schema manually.
You say: "Because the join table generated by your first schema generation has a foreign key reference to car it keeps your data inserted into the first schema in a no more deletable state."
But this join table is not used anymore, and I don't see why the VSChema should not manage this case. To my mind, he should be able to detect that this table is not used, and delete it.
Anyway, another solution for me would be either to delete this table from my application, or at least to be able to empty it, so that no integrity errors would occur anymore when deleting my cars and their new single wheels.
Is there a way to do that?
yes, you can do that.
We do not want to delete tables that have nothing to do with us. With MS Sql Server it might be OK to clean up, but if you think about Oracle where many schemas are in one database you do not want to drop the tables of you collogues.
All the best,
the Telerik team
I'm sorry to insist, by I do not know how to remove or clear the table from the application, as
- The table is not known and cannot be accessed by an OQL query
- The SQL queries forbid any other instruction than SELECT
Any help would be welcome,
There are several ways to do it. One is to use vschema.exe on the client side to execute an sql script that drops the table.
If you want to do it inside your code you can open an ADO.NET connection in parallel and execute the drop directly. Or you can make a dummy class without fields that maps to the join table (just set the table name). This will drop the additonal columns then.
the Telerik team