How are SYS_C000 constraints handled between a dev db and test/production?
GaffSep 18 2009 — edited Sep 18 2009Hi:
I'm on a project that has a development db which app developers use to develop against. There is an identical (at some point in time) database for test and production. Unfortunately there are constraints in dev that have system generated names (e.g. SYS_C00123).
The way we keep the two target DBs in sync with dev is that when we deliver a new version of the application code, we use Toad to create a db migration script. Toad diffs the schemas and figures out that in order to make the target identical to the source db you have to issue a "create table foo..." etc., etc. SQL Developer can apparently do the same.
All well and good until a system named constraint needs to be altered. Toad will sometimes issue things like
ALTER TABLE FOO
MODIFY(FOO_KEY CONSTRAINT SYS_C005256 NOT NULL);
Except that constraint has a different name in the other two db's because it is system generated.
I suspect the answer is to eventually make sure that all constraints are given a name by us, not Oracle, but I was wondering what other people have done when finding themselves in this unfortunate type of situation.
Thanks.