Chapter 3 DBMS Resource File Reference

Column

The Column category is located in the Root→Script→Objects category, and can contain the following items that define how columns are modeled for your DBMS.

Item Description
[Common
items] 		The following common object items may be defined for columns:

  • Add
  • AfterCreate, AfterDrop, AfterModify
  • BeforeCreate, BeforeDrop, BeforeModify
  • ConstName
  • Create, Drop
  • Enable
  • Maxlen, MaxConstLen
  • ModifiableAttributes
  • Options, DefOptions
  • Permission
  • ReversedQueries, ReversedStatements
  • SqlAttrQuery, SqlListQuery, SqlOptsQuery, SqlPermQuery


For a description of each of these common items, see Common object items.
AddColnCheck Specifies a statement for customizing the script for modifying column constraints within an alter table statement.

Example (Oracle 10g): 

alter table [%QUALIFIER%]%TABLE%
   add [constraint %CONSTNAME%] check (%.A:CONSTRAINT%)
AlterTableAdd
Default 		Specifies a statement for defining the default value of a column in an alter statement.

Example (SQL Server 2005): 

[[ constraint %ExtDeftConstName%] default %DEFAULT% ]for %COLUMN%
AltEnableAdd
ColnChk 		Specifies if a column check constraint, built from the check parameters of the column, can or cannot be added in a table using an alter table statement. The following settings are available:

  • Yes - AddColnChck can be used to modify the column check constraint in an alter table statement.
  • No - PowerDesigner copies data to a temporary table before recreating the table with the new constraints.


See also AddColnChck .
AltEnableTS
Copy 		Enables timestamp columns in insert statements.
Bind Specifies a statement for binding a rule to a column.

Example (ASE 15): 

[%R%?[exec ]][execute ]sp_bindrule [%R%?['[%QUALIFIER%]%RULE%'][[%QUALIFIER%]%RULE%]:['[%QUALIFIER%]%RULE%']], '%TABLE%.%COLUMN%'
CheckNull Specifies whether a column can be null.
Column
Comment 		Specifies a statement for adding a comment to a column.

Example: 

comment on column [%QUALIFIER%]%TABLE%.%COLUMN% is %.q:COMMENT%
DefineColn
Check 		Specifies a statement for customizing the script of column constraints (checks) within a create table statement. This statement is called if the create, add, or alter statements contain %CONSTDEFN%.

Example: 

[constraint %CONSTNAME%] check (%CONSTRAINT%)
DropColnChck Specifies a statement for dropping a column check in an alter table statement. This statement is used in the database modification script when the check parameters have been removed on a column.

If DropColnChck is empty, PowerDesigner copies data to a temporary table before recreating the table with the new constraints.

Example (SQL Anywhere 10): 

alter table [%QUALIFIER%]%TABLE%
   drop constraint %CONSTNAME%
DropColnComp Specifies a statement for dropping a column computed expression in an alter table statement.

Example (SQL Anywhere 10): 

alter table [%QUALIFIER%]%TABLE%
   alter %COLUMN% drop compute
DropDefault
Constraint 		Specifies a statement for dropping a constraint linked to a column defined with a default value

Example (SQL Server 2005): 

[%ExtDeftConstName%?alter table [%QUALIFIER%]%TABLE%
   drop constraint %ExtDeftConstName%]
EnableBindRule Specifies whether business rules may be bound to columns for check parameters. The following settings are available:

  • Yes - The Create and Bind entry of Rule are generated
  • No - The check is generated inside the column Add order
Enable
ComputedColn 		Specifies whether computed columns are permitted.
EnableDefault Specifies whether predefined default values are permitted. The following settings are available:

  • Yes - The default value (if defined) is generated for columns. It can be defined in the check parameters for each column. The %DEFAULT% variable contains the default value. The Default Value check box for columns must be selected in the Tables & Views tabs of the Database Generation box
  • No - The default value can not be generated, and the Default Value check box is unavailable.


Example (AS IQ 12.6):

EnableDefault is enabled and the default value for the column employee function EMPFUNC is Technical Engineer. The generated script is: 

create table EMPLOYEE
(
  EMPNUM      numeric(5)        not null,
  EMP_EMPNUM  numeric(5)                ,
  DIVNUM      numeric(5)        not null,
  EMPFNAM     char(30)                  ,
  EMPLNAM     char(30)          not null,
  EMPFUNC     char(30)                   
      default 'Technical Engineer',
  EMPSAL      numeric(8,2)              ,
  primary key (EMPNUM)
);
EnableIdentity Specifies whether the Identity keyword is supported. Identity columns are serial counters maintained by the database (for example Sybase and Microsoft SQL Server). The following settings are available:

  • Yes - Enables the Identity check box in the column property sheet.
  • No - The Identity check box is not available.


When the Identity check box is selected, the Identity keyword is generated in the script after the column data type. An identity column is never null, and so the Mandatory check box is automatically selected. PowerDesigner ensures that:

  • Only one identity column is defined per table
  • A foreign key cannot be an identity column
  • The Identity column has an appropriate data type. If the Identity check box is selected for a column with an unsupported data type, the data type is changed to numeric . If the data type of an identity column is changed to an unsupported type, the error Identity cannot be used with the selected data type is displayed.


Note that, during generation, the %IDENTITY% variable contains the value "identity" but you can easily change it, if needed, using the following syntax : 

[%IDENTITY%?new identity keyword]
EnableNotNull
WithDflt 		Specifies whether default values are assigned to columns containing Null values. The following settings are available:

  • Yes - The With Default check box is enabled in the column property sheet. When it is selected, a default value is assigned to a column when a Null value is inserted.
  • No - The With Default check box is not available.
ModifyColn
Chck 		Specifies a statement for modifying a column check in an alter table statement. This statement is used in the database modification script when the check parameters of a column have been modified in the table.

If AddColnChck is empty, PowerDesigner copies data to a temporary table before recreating the table with the new constraints.

Example (AS IQ 12.6): 

alter table [%QUALIFIER%]%TABLE%
   modify %COLUMN% check (%.A:CONSTRAINT%)


The %COLUMN% variable is the name of the column defined in the table property sheet. The % CONSTRAINT % variable is the check constraint built from the new check parameters.

AltEnableAddColnChk must be set to YES to allow use of this statement.
ModifyColn
Comp 		Specifies a statement for modifying a computed expression for a column in an alter table.

Example (ASA 6): 

alter table [%QUALIFIER%]%TABLE%
   alter %COLUMN% set compute (%COMPUTE%)
ModifyColnDflt Specifies a statement for modifying a column default value in an alter table statement. This statement is used in the database modification script when the default value of a column has been modified in the table.

If ModifyColnDflt is empty, PowerDesigner copies data to a temporary table before recreating the table with the new constraints.

Example (ASE 15): 

alter table [%QUALIFIER%]%TABLE%
   replace %COLUMN% default %DEFAULT%


The %COLUMN% variable is the name of the column defined in the table property sheet. The %DEFAULT% variable is the new default value of the modified column.
ModifyColnNull Specifies a statement for modifying the null/not null status of a column in an alter table statement.

Example (Oracle 10g): 

alter table [%QUALIFIER%]%TABLE%
   modify %COLUMN% %MAND%
ModifyColumn Specifies a statement for modifying a column. This is a different statement from the alter table statement, and is used in the database modification script when the column definition has been modified.

Example (SQL Anywhere 10): 

alter table [%QUALIFIER%]%TABLE%
   modify %COLUMN% %DATATYPE% %NOTNULL%
NullRequired Specifies the mandatory status of a column. This item is used with the NULLNOTNULL column variable, which can take the "null", "not null" or empty values. For more information, see Working with Null values.
Rename Specifies a statement for renaming a column within an alter table statement.

Example (Oracle 10g): 

alter table [%QUALIFIER%]%TABLE% 
   rename column %OLDCOLN% to %NEWCOLN%
SqlChckQuery Specifies a SQL query to reverse engineer column check parameters. The result must conform to proper SQL syntax.

Example (SQL Anywhere 10): 

{OWNER, TABLE, COLUMN, CONSTNAME, CONSTRAINT}
select u.user_name, t.table_name, 
   c.column_name, k.constraint_name,
   case(lcase(left(h.check_defn, 5))) when 'check' then substring(h.check_defn, 6) else h.check_defn end
from sys.sysconstraint k
   join sys.syscheck h on (h.check_id = k.constraint_id)
   join sys.systab t on (t.object_id = k.table_object_id)
   join sys.sysuserperms u on (u.user_id = t.creator)
   join sys.syscolumn c on (c.object_id = k.ref_object_id)
where k.constraint_type = 'C'
[  and u.user_name=%.q:OWNER%]
[  and t.table_name=%.q:TABLE%]
[  and c.column_name=%.q:COLUMN%]
order by 1, 2, 3, 4
SqlStatistics Specifies a SQL query to reverse engineer column and table statistics.

Example (ASE 15): 

[%ISLONGDTTP%?{ AverageLength }
select [%ISLONGDTTP%?[%ISSTRDTTP%?avg(char_length(%COLUMN%)):avg(datalength(%COLUMN%))]:null] as average_length
from [%QUALIFIER%]%TABLE%
:{ NullValuesRate, DistinctValues, AverageLength }
select
[%ISMAND%?null:(count(*) - count(%COLUMN%)) * 100 / count(*)]  as null_values,
[%ISMAND%?null:count(distinct %COLUMN%)]  as distinct_values,
[%ISVARDTTP%?[%ISSTRDTTP%?avg(char_length(%COLUMN%)):avg(datalength(%COLUMN%))]:null] as average_length
from [%QUALIFIER%]%TABLE%]
Unbind Specifies a statement for unbinding a rule to a column.

Example (ASE 15): 

[%R%?[exec ]][execute ]sp_unbindrule '%TABLE%.%COLUMN%'

 

Copyright (C) 2008. Sybase Inc. All rights reserved.