Forward Engineer/Alter Script › Check Your Model/Validate SQL › Validation Criteria for Teradata Models

Validation Criteria for Teradata Models

The Check Model/Validate SQL option allows you to reduce occurrences of syntax errors during forward engineering. For Teradata physical models, the following objects and properties are validated during the Check Model process:

Cast

• The target or source data type must be a user defined type.
• A procedure to perform the cast must be specified. The procedure must also be deterministic.

Column

• If the column is a user defined type, only a default value of NULL is allowed.
• For a timestamp column on a queue table, only a default value of current_timestamp is allowed.

Entity

• An identity column can not take part in a referential constraint.
• If uniqueness constraints, then row control can not be multiset.
• A table containing a UDT column must have at least one non-UDT column.
• The first column of the table cannot be a UDT if the primary key or unique attribute is not specified for any non-UDT column.
• A base table cannot have more then 32 LOB columns (including UDT LOB columns).
• Must have one non-LOB column.
• A queue or global temp trace table does not allow columns of LOB data types.
• For a global temporary trace table , the first column must be proc_ID byte(2). The second column must be a sequence integer.
• For a queue table then first column must be a Query Insertion Timestamp (timestamp not null default current_timestamp) and precision is either not defined or 6.
• On an identity column, the start with value must be less than the maxium value for an incremental series and less than the minimum value for a decremental series.
• For an identity column, the increment must be negative if no minimum value is specified.
• For an identity column with a positive increment, the maximum value must be greater than the start value.
• For an identity column, the increment must be positive if no maximum value is specified.
• If generated identity is specified, column must be defined with an exact numeric data type (byteint, decimal (n,0),integer, numeric(n,0), smallint) non valid types include (datetime and interval, decimal(n,m) where m is not 0, double precision, float, numeric(n,m) where m is not 0 and real).
• Foreign keys not allowed if UDT or LOB column.
• A check constraint can not be a LOB or User Defined Type column.

Function

• If a return cast to a UDT is specified, a cast must exist where the source of the cast is the UDT in the CAST FROM clause and the target of the cast is the return data type of the function.
• The external name, language, SQL data access, return data type and name must be present.

Keygroup

• A Hash index can not be defined on a table with triggers.
• Partitioning is only valid on the primary index of a table.
• If primary index columns are specified for a Hash Index then an order by clause must be specified.
• A hash index is not allowed on a table which has journaling.
• A maximum of 64 foreign keys can be defined for a single table.
• A maximum of 64 columns can be specified for an index (pk or fk).
• No LOB or UDT columns as key member of secondary index.
• Identity columns cannot be part of composite primary index, composite secondary index, join index, hash index, partitioned primary index, and value-ordered index.
• Primary index members can not be compressed.
• Query Insertion Timestamp columns (on queue tables) can't be part of unique and pk constraints.
• A primary index member cannot be a UDT or LOB data type.
• If Order by is specified is set then column name must be numeric with four bytes or less and the column data type must be date, byteint, integer, decimal, smallint.
• The data type of columns used for hash ordering cannot be a UDT or LOB.
• The columns used for value ordering must be of a data type containing 4 or fewer bytes.

Method

• A method specified for a cast or ordering may not have any parameters.
• If the return data type is a UDT and a specific name is given to the method, the language must be specified.
• If a cast to a UDT is specified, a cast must exist where the source of the cast is the UDT and the target of the cast is the return data type.
• If a delimiter is specified, the entry point name must be specified.
• The external name and entry point name must be specified together for the EXTERNAL clause.

Parameter

• Only 1 parameter may be specified on a function used by a cast or ordering.
• If one parameter is specified, then all parameter names must be specified within the function.
• A data type must be specified for all parameters.

Procedure

• For external procedures, an entry point name and delimiter must be specified.
• For external procedures, a name, data access and language must be specified.

Profile

• The minimum length allowed for a password must be less than or equal to the maximum length allowed.
• The maximum length allowed for a password must be greater than or equal to the minimum length allowed.

Replication Group

• A replication group must contain at least 1 table.
• Tables which have hash indexes defined cannot be part of a replication group.
• Tables that contain LOB or UDT columns can not be included in the replication group definition.
• A replicated table can have a maximum of 1000 columns.

User Defined Types

• The procedures used for the transform must be deterministic.
• An ordering procedure must be specified for the user defined type if a transform is defined.
• The data type of the parameter of the To Procedure must match the data type of the return value of the From Procedure.
• The To SQL procedure must have 1 parameter and only 1 parameter.
• The transform to convert to SQL must have a result data type of the UDT.

UserId, Database

• Permanent space must be specified for a user or database.