I am deploying my first app.
I am little bit confused how to approach database updates.
I have one database which is development database and one which is production.
If i buildJar with diferent database setings schema is correctly created but than it fails on creating foreign keys.
If i change database in “main database” setting in cuba for production and deploy everything is updated correctly.
What is correct development procedure with multiple databases. etc?
Hi,
Generally if your production database is consistent with your development database - it means that you are managing DB schema for all databases only through database update scripts - then there should be no problems with updating production database.
However sometimes still you can encounter unexpected situations.
For example, you added a non-null constraint to an existing column. In development database such update script could run successfully because there are no conflicting rows having null value in the column.
Then you update your production environment - and DB update suddenly fails! You start analyzing error message given by the database and understand that some values in the table had conflicting NULL value in the column.
In case of encountering problems you might want to manually adjust code of the problematic update script, maybe even completely clear its contents. Empty update script will be run and remembered, without causing problems.
E.g. for adding non-null constraint to an existing column you might want to code something like this (actually Studio generates similar scripts):
update STUDENT set BIRTH_DATE = '1970-01-01' where BIRTH_DATE is null;
alter table STUDENT alter column BIRTH_DATE set not null;
Changing database schema is not trivial when it happens for an existing database holding production data. You lose some level of freedom and can’t do renames, drops and datatype changes as freely as it was on the prototype stage.
Regarding your particular problem - you should look into system logs and query database contents to investigate why your particular update script failed to create foreign keys, to understand what data in the database prevented it. And then invent a way to work around it, or completely revise previous engineering decision made during development.