11 Nov 1996 updated
PowerDesigner V5.x has a conditional check box which will appear at the lower right of the PDM Columns of Table: xxx window for those databases which support the identity or serial data type concept:
"(For Sybase System 10, Sybase System 11, and MS SQL Server 6 only) When selected, indicates that the column is an identity column."
CDM Model Check does give an error on any entity with more than one attribute of data type serial.
Setting on Identity for any column of a PDM table disables that property for all other columns.
PowerDesigner’s use of the term "precision" is reversed from that used by Sybase. PowerDesigner refers to a data item’s "length" and "precision", whereas Sybase refers to "precision" and "scale". The on-line help for Sybase SQL Anywhere V5 defines a decimal number as "A decimal number with precision total digits and with scale of the digits after the decimal point."
Sybase docs say "You can define an IDENTITY column with any desired precision - from 1 to 38 digits …" Thus, at least for Sybase, some explicit column length is required.
Yet PowerDesigner does not require any data type length for a type Serial in the CDM. Any data type of Serial is automatically marked as Identity in the PDM, with data type Number. If the CDM data item had a length it will be generated with the same length (or none) into the PDM. This can result in an Identity column with a zero length - no allowed by Sybase (and we presume any other database).
Furthermore, if you choose another column to be the Identity column (first setting off Identity if any column holds it)), if the new Identity column has any "precision", it immediately looses both its length and "precision", which PowerDesigner blanks. It seems that if there is no "precision" present when you select Identity, then the length value is not blanked.
Sybase System 10 docs say an identity column must be "of type numeric and scale zero". Regardless of the terminology mismatch, PowerDesigner does enforce this rule in at least two ways which prohibit any decimal portion ("precision" to PowerDesigner or "scale" to Sybase") in an identity column.
In the CDM this is done by disabling the "precision" field in the data type selection window for a data type of Serial.
In the PDM this is done somewhat clumsily. Data type entry allows a precision for serial. However this is rejected by an error message which pops up as you exit from the Columns of table: xxx window if you have assigned any "precision" to an identity column. The error message, "Identity cannot be used with the selected data type", is neither clear nor timely but it does enforce the prohibition on a decimal portion.
If there is currently no Identity column in a table, then the Identity option will be enabled for every data type except Int, including Small Int and Tiny Int.
Setting the Identity property off any column does not restore the original data type or "precision", which are left blank.
As we get permission from the various users who have written on this issue, we will be adding their observations and comments here.
Working in the physical model, I was cleaning up my domains. This involved reviewing all the datatypes, and applying the requisite domains to the columns.
Using
Org -< OrgAddr >- Addr
where
Org(id) is of domain org_id (integer, identity)
Addr(id) is of domain addr_id (integer, identity)
two ?bugs? become evident.
OrgAddr cacks with "only one serial column allowed per table" (or something), and it looked like checking the identity box for the column automagically set the datatype to numeric. Correct me if I'm wrong, but I believe that MS SqlServer 6.5 has identity ints, smallints and tinyints.
The first is more serious. This shows that PowerDesigner fails to recognize that the identifier attribute can never be held by a domain shared between two entities. On a day chock full of unreal expectations, I'd find that the method I used working, with that entity who's PK is solely represented by the (id) value would hold the 'identity' modifier, all others recognized as FK's and not deemed serial. Alas.
I tried the following and also ran into problems:
All is cool so far, and good practise as well. Now, generate a PDM from this structure:
THIS is now the same problem Barry found.
If the CDM recorded identities you would expect the identity property to have been discarded when the identifiers were projected along the relationships to form FKs, maybe by automatically creating a separate non-identity domain. Anyway, its a problem that S-D should recognize and deal with as an identity FK is obvious nonsense.
I just tried this using 16-bit S-D 5.0 under W3.11and can't reproduce Barry's effect because this version won't let me declare either an integer domain or a non-domain integer column to be an identity. Unlike Barry's case S-D made no attempt to change the domain data type - it just said 'wrong data type for an identity'. Rather than spending further time I gave up at that point.