|
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? Thanks Jim Diaz |
|
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. FWIW, one of my favourite "small" improvements in v9:)
(07 May '12, 09:34)
Volker Barth
|
|
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. Jim Diaz |
There is a big difference between ASA 8 (and before) and ASA 9 (and above) w.r.t. the storage of check constraints - ASA 9 introduced them as "named constraints" so each single check constraint is stored as a separate database object, whereas ASA 8 combines all table checks for one table as an ANDed "anonymous" constraint in SYSTABLE.View_Def (as you state).
But do you miss these constraints for the upgraded databases, i.e. are their values in SYSTABLE.View_Def and SYSCOLUMN.CHECK empty?
I do not know but would expect that DBUPGRADE on a v8 database to v9 would not change the storage of the check constraints.