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%'
Parent topic: Script/Objects Category