Cannot alter table - integrity constraint violation:unique v

Posted by Admin on 31-Mar-2008 16:05

I have several existing tables (OE 10.1c) which I am attempting to add a column to. I need to add a timestamp to several tables using the following statement:

ALTER TABLE tableA ADD changed_timestamp TIMESTAMP NOT NULL DEFAULT SYSTIMESTAMP;

. The problem is it will work fine on all but two tables. On those particular tables, the statement above yields the error:

Integrity constraint violation:uniqueness constraint violation (7518)

What constraint could prevent me from altering these tables? It might be worth mentioning that I cannot add any data type to these tables. If I try to add an integer of any given name, the same error is shown. Any comments or suggestions would be appreciated. Thanks

All Replies

Posted by Admin on 31-Mar-2008 19:37

Hi Tom,

I suspect that the field already exists in that table, try

SELECT col FROM syscolumns WHERE tbl = 'tableA'

and also get someone to check for its existance from within the 4gl DataDictionary. It may also be that the column exists in this table but not within the schema you have attached.

Also a heads-up on some of "features" you may discover down the path;

1) schema changes performed via DDL in and ODBC requests will only be visible to p4gl code if they are done within the PUB schema. You can find yourself in a nasty situation where your columns/tables cannot be seen from your legacy p4gl app.

2) be careful with support for all SQL92 data types from within your p4gl legacy app. You may find the p4gl developers/app getting an error like "** Incompatible data types in expression or assignment. (223)", which after some digging around you will come across the statement "Not all the SQL-92 data types such as TIMESTAMP are supported by the 4GL." ... and notice that you are creating a TIMESTAMP column.

3) The compiled p4gl code contains logic to validate changes to the schema and requires recompilation if any table that the program directly references has its definition changed ... so by adding this column may cause some of the compiled programs not to load. This depends very much on the type of p4gl used, for example dynamic data access statements do not suffer from this as they are late bound - but many legacy p4gl apps will be early bound into the schema.

Have not check the state of play with 10.1c but you may want to do your own internal checks on the above.

- Colin

P.S. Trying hard not to insert any 'April fool' leads in replies ... so far, so good

Posted by Admin on 01-Apr-2008 11:49

Colin, thanks for the reply. I performed the select changed_timestamp from syscolumns where tbl = 'tableA'. I get the message "changed_timestamp" cannot be found or is not specified for query. I do see all the other rows when I do a select *.

1.) I am connecting using , so I should be ok there. 2.) This new timestamp will be used only by my interface, (and the 4gl triggers, I suppose) to detect changed records, it never needs to be read or written by their 4gl app. Also the triggers are updating the timestamps correctly so I think that portion is working. 3.) A recompilation was performed just get the app working, I suppose now I know why! The app does appear to still be working fine.

I still cannot alter those two particular tables at all using any column name or data type (which leads me to believe it's not really a uniqueness constraint, at least in the usual sense). Do you know of any table level constraints or similar validation enforcement methods that OE uses to not allow a user, even dba, to not be able to alter the table? Thanks for all the info.

Posted by Thomas Mercer-Hursh on 01-Apr-2008 12:22

Have you looked in the Data Dictionary screens for things like whether the table is frozen?

Posted by Admin on 01-Apr-2008 12:34

I just checked. The "hidden" and "frozen" checkboxes are unchecked in the data dictionary. I sincerely hoped it would be that easy, however, once again PROGRESS figures out a way to get me totally stumped.

Posted by Admin on 01-Apr-2008 12:42

For a further update, I have the exact same database file loaded in two environments (OE 10.1.A02/full) and (OE 10.1C/eval). In 10.1A, I can alter these two tables, just like I would expect. In the 10.1C eval, I cannot alter these two, and only these two tables (others alter fine). I suppose it could be another problem with the evaluation version(I have had several recently). Unfortunately, the environment I am forced to work in is the 10.1C eval, for which I cannot alter these tables!

Posted by Thomas Mercer-Hursh on 01-Apr-2008 13:07

Not that it solves the mystery, but have you tried adding the fields from the Data Dictionary just so that you can get on with your work?

Posted by Admin on 01-Apr-2008 13:15

OMG!!!! At first I thought this would not work, as the data dictionary is read-only with the eval version. But it was definitely worth a shot. I started up the data dictionary with the -rx paramter which is supposed to allow some writes. Sure enough I could create a field changed_timestamp, though through the data dictionary the type is referred to as Datetime. Regardless, I can continue now. Thanks for your great suggestion

Posted by Thomas Mercer-Hursh on 01-Apr-2008 13:29

Well, if you can't get there one way ...

This thread is closed