On Jun 21, 4:05 pm, JaxenT <jax...@gmail.com> wrote:
> I am trying to resolve a SE dbschema/dbexport problem. I need to
> either rename a bunch of not null constraints or unload, drop, create
> and load several tables. I get bit either way I go.
>
> I tried to cheat and just change the name in SYSCONSTRAINTS. That
> gives a "
> -397 - System catalog (sysobjstate) corrupted" error.
>
> Then I tried to do an ALTER TABLE to drop then add the constraint.
> Well some of the problem constraints are on tables with serial field
> so it would not let me drop them. But even worst it doesn't seem that
> SE will allow you to add a null constraint. "ALTER TABLE xredit_cards
> ADD CONSTRAINT NOT NULL (cc) CONSTRAINT ccNull" gives me a syntax
> error at NOT.
>
> OK so I'll write a 4GL script to unload, drop, create and load. The
> following create gives a compile error in 4GL, the statement works
> fine in dbaccess.
> create table "rich".credit_cards
> (
> cc char(2) not null constraint cc_null,
> descr char(20)
> )
>
> Any of you smart folks got an idea of the best course to go on this.
> I need to do it in a script so that I can do it in production later
> after a few passes on the devel box.

NOT NULL constraints cannot be 'ADDED' they have to be MODIFIED:

ALTER TABLE xredit_cards MODIFY (somecolumn some type NOT NULL
CONSTRAINT some_constr_name);

But, why struggle. When you dbexport, have dbexport write the SQL
file to disk and just edit the disk file to change the constraint
names in the CREATE TABLE statements in the file. Alternatively, for
others reading htis who are not using SE - sonce we've already
determined that myschema no longer works for SE databases (sigh) - you
can use the '-l' option to myschema to create a dbimport compatible
schema with the constraints renamed for you (actually for NOT NULL
constraints I just drop the constraint names so that the new database
creates new names.

Art S. Kagel