SEO

#1 (**permalink**) 04-20-2008, 03:50 PM

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.

#2 (**permalink**) 04-20-2008, 03:50 PM

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

#3 (**permalink**) 04-20-2008, 03:51 PM

Oh if I could ... But dbexport needs to use dbschema to work and
dbschema is failing with -100 errors. The alter table worked but that
did not fix the 100 errors. I am just building a huge brute force sql
file to run instead of trying to be smart and doing a program to
rebuild it all. I've gone through 50 of 180 tables and 38 so far have
to be rebuilt. I have four other dbs of about the same size to do as
well, I bet the problems there will be about the same. It is mind
nulling. Thanks for all your help though.

>
> 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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Creating Not Null contraints in SE