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 datatype 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 can't 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 datatypes.
• 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 datatype of the function.
• The external name, language, SQL data access, return datatype 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 datatype 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 datatype 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 delimeter 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 delimeter 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.