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

asked 07 May '12, 06:20

J%20Diaz's gravatar image

J Diaz
1.2k384665
accept rate: 10%

edited 07 May '12, 09:04

Glenn%20Paulley's gravatar image

Glenn Paulley
10.8k576106

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.

(07 May '12, 07:33) Volker Barth

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.

permanent link

answered 07 May '12, 09:13

Glenn%20Paulley's gravatar image

Glenn Paulley
10.8k576106
accept rate: 43%

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

permanent link

answered 07 May '12, 08:47

J%20Diaz's gravatar image

J Diaz
1.2k384665
accept rate: 10%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×11

question asked: 07 May '12, 06:20

question was seen: 2,535 times

last updated: 07 May '12, 09:34