I've been reviewing some 9.0.2 remote databases which have been around since SQL Anywhere 6. Several do not have the system table sys.SysConstraint. I notice when adding a new table check constraint on these databases that it is stored in the SysTable.View_Def column and that the Sys.SysColumn table on these databases has a "Check" column.
I believe the cause is the use of DBUpgrad on some databases when we moved from 6 to 9. Did I lose my table and column check constraints on those databases which were upgraded rather then unloaded and reloaded? What other issues may I have on these databases?
Named constraints required a store format change in order to maintain compatibility with extant older databases, which is why a dbupgrad will not result in the new sys.sysconstraint catalog table being created.
This is documented in the version 9 help (new features in ASA V9) as:
Constraints can now be named Check constraints, unique constraints, and referential integrity constraints can now be assigned names. This permits modification of table and column constraints by changing individual constraints, rather than by modifying an entire table constraint.
To gain the benefits of this enhancement on databases created before this release, you must upgrade the database file format by unloading and reloading the database.
answered 07 May '12, 09:13
Thanks very much. I think there was some confusion due to the way Sybase Central displays the check constraints when viewing a non-upgraded ASA9 vs. one upgraded from a previous version. It looks as if all the constraints are preserved, both table and column.
I am a bit surprised the dbupgrade utility doesn't change the system table formats.
Thanks again for your help.
answered 07 May '12, 08:47