Table

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

Item

Description

[Common items]

The following common object items may be defined for tables:

  • AfterCreate, AfterDrop, AfterModify

  • BeforeCreate, BeforeDrop, BeforeModify

  • ConstName

  • Create, Drop

  • Enable, EnableSynonym

  • Header, Footer

  • 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.

AddTableCheck

Specifies a statement for customizing the script to modify the table constraints within an alter table statement.

Example (SQL Anywhere 10):

alter table [%QUALIFIER%]%TABLE%
  add [constraint %CONSTNAME% ]check (%.A:CONSTRAINT%)

AllowedADT

Specifies a list of abstract data types on which a table can be based. This list populates the Based On field of the table property sheet.

You can assign an abstract data type to a table, the table will use the properties of the type and the type attributes become table columns.

Example (Oracle 10g):

OBJECT

AlterTable Footer

Specifies a statement to be placed after alter table statements (and before the terminator).

Example:

AlterTableFooter = /* End of alter statement */

AlterTable Header

Specifies a statement to be placed before alter table statements. You can place an alter table header in your scripts to document or perform initialization logic.

Example:

AlterTableHeader = /* Table name: %TABLE% */

DefineTable Check

Specifies a statement for customizing the script of table constraints (checks) within a create table statement.

Example:

check (%CONSTRAINT%)

DropTable Check

Specifies a statement for dropping a table check in an alter table statement.

Example:

alter table [%QUALIFIER%]%TABLE%
  delete check

InsertIdentityOff

Specifies a statement for enabling insertion of data into a table containing an identity column.

Example (ASE 15):

set identity_insert [%QUALIFIER%]%@OBJTCODE% off

InsertIdentityOn

Specifies a statement for disabling insertion of data into a table containing an identity column.

Example (ASE 15):

set identity_insert [%QUALIFIER%]%@OBJTCODE% on

Rename

[modify] Specifies a statement for renaming a table. If not specified, the modify database process drops the foreign key constraints, creates a new table with the new name, inserts the rows from the old table in the new table, and creates the indexes and constraints on the new table using temporary tables.

Example (Oracle 10g):

rename %OLDTABL% to %NEWTABL%

The %OLDTABL% variable is the code of the table before renaming, and the %NEWTABL% variable is the new code.

SqlChckQuery

Specifies a SQL query to reverse engineer table checks.

Example (SQL Anywhere 10):

{OWNER, TABLE, CONSTNAME, CONSTRAINT}
select u.user_name, t.table_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)
where k.constraint_type = 'T'
 and t.table_type in (1, 3, 4)
[  and u.user_name = %.q:OWNER%]
[  and t.table_name = %.q:TABLE%]
order by 1, 2, 3

SqlListRefr Tables

Specifies a SQL query used to list the tables referenced by a table.

Example (Oracle 10g):

{OWNER, TABLE, POWNER, PARENT}
select c.owner, c.table_name, r.owner,
 r.table_name
from sys.all_constraints c,
 sys.all_constraints r
where (c.constraint_type = 'R' and c.r_constraint_name = r.constraint_name and c.r_owner = r.owner)
[ and c.owner = %.q:SCHEMA%]
[ and c.table_name = %.q:TABLE%]
union select c.owner, c.table_name,
 r.owner, r.table_name
from sys.all_constraints c,
 sys.all_constraints r
where (r.constraint_type = 'R' and r.r_constraint_name = c.constraint_name and r.r_owner = c.owner)
[ and c.owner = %.q:SCHEMA%]
[ and c.table_name = %.q:TABLE%]

SqlListSchema

Specifies a query used to retrieve registered schemas in the database. This item is used with tables of XML type (a reference to an XML document stored in the database).

When you define an XML table, you need to retrieve the XML documents registered in the database in order to assign one document to the table, this is done using the SqlListSchema query.

Example (Oracle 10g):

SELECT schema_url FROM dba_xml_schemas

SqlStatistics

Specifies a SQL query to reverse engineer column and table statistics. See SqlStatistics in Column.

SqlXMLTable

Specifies a sub-query used to improve the performance of SqlAttrQuery (see Common object items).

TableComment

[generation and reverse] Specifies a statement for adding a table comment. If not specified, the Comment check box in the Tables and Views tabs of the Database Generation box is unavailable.

Example (Oracle 10g):

comment on table [%QUALIFIER%]%TABLE% is
%.q:COMMENT%

The %TABLE% variable is the name of the table defined in the List of Tables, or in the table property sheet. The %COMMENT% variable is the comment defined in the Comment textbox of the table property sheet.

TypeList

Specifies a list of types (for example, DBMS: relational, object, XML) for tables. This list populates the Type list of the table property sheet.

The XML type is to be used with the SqlListSchema item.

UniqConstraint Name

Specifies whether the same name for index and constraint name may be used in the same table. The following settings are available:

  • Yes – The table constraint and index names must be different, and this will be tested during model checking

  • No - The table constraint and index names can be identical