9334 Invalid constraint definition

Database
Win
Teradata Employee

9334 Invalid constraint definition

This error was received after executing the following DDL statement for bitemporal table:

CREATE MULTISET TABLE Policy,
NO FALLBACK,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO
(
Col1 BYTEINT NOT NULL
Col2 BYTEINT NOT NULL,
TTCol PERIOD(TIMESTAMP(6) WITH TIME ZONE) NOT NULL AS TRANSACTIONTIME,
VTCol PERIOD(DATE) NOT NULL AS VALIDTIME,
CONSTRAINT CurrentCol1 CURRENT VALIDTIME UNIQUE (Col1)
) PRIMARY INDEX (Col2);

According to Teradata documentation ("Messages"),

This error can occur for any of the following reasons:

  1. A hard RI is being defined on a temporal table, Hard RI is not allowed on temporal tables.
  2. The constraint definition is specifying a ValidTime qualifier option and the table does not support ValidTime.
  3. The constraint definition is specifying a TransactionTime qualifier option and the table does not support TransactionTime

but;

  1. No RI constraint was defined for this table.
  2. The table can be created without TRANSACTIONTIME and the DDL statement runs successfully. (ValidTime is supported.)
  3. The table can be created without VALIDTIME and CONSTRAINT and the DDL statement runs successfully (TransactionTime is supported.)

Is/Are there any other reason(s) why the DDL statement fails? Thank you in advance for any help that will be given.

6 REPLIES
Junior Contributor

Re: 9334 Invalid constraint definition

What's your exact Teradata release?

Your Create works fine for me on a 15.00.01.06.

Win
Teradata Employee

Re: 9334 Invalid constraint definition

Hi Dieter,

I am using Teradata 15.00.01.01. Thank you.

Enthusiast

Re: 9334 Invalid constraint definition

Hi This is due to ANSI Temporal syntax support feature which was introduced 15.0. From this feature onwards user must specify fully qualified constraints. Below SQL works.

CREATE MULTISET TABLE Policy,

NO FALLBACK,

NO BEFORE JOURNAL,

NO AFTER JOURNAL,

CHECKSUM = DEFAULT,

DEFAULT MERGEBLOCKRATIO

(

   Col1 BYTEINT NOT NULL

   Col2 BYTEINT NOT NULL,

   TTCol PERIOD(TIMESTAMP(6) WITH TIME ZONE) NOT NULL AS TRANSACTIONTIME,

   VTCol PERIOD(DATE) NOT NULL AS VALIDTIME,

   CONSTRAINT CurrentCol1 CURRENT VALIDTIME AND CURRENT TRANSACTIONTIME UNIQUE (Col1)

) PRIMARY INDEX (Col2);

Win
Teradata Employee

Re: 9334 Invalid constraint definition

I have tried the original query in Teradata Release 15.00.01.08 and the query also worked fine.

So my conclusion is that, the original query that I used works on the latest releases of Teradata 15.

I have also tried Anil's query and it also worked on the previous Teradata Release 15.00.01.01 that I used when my query failed.

Thanks to both of you, Dieter and Anil, for replying to my post.

Enthusiast

Re: 9334 Invalid constraint definition

Hi , Do you have dbscontrol access?. Just let me know, what is the value given for  below flag in general.

88. TemporalBehavior

Win
Teradata Employee

Re: 9334 Invalid constraint definition

Hi Anil,

Temporal Behavior is set to 1 (ANSI Temporal Behavior). Thank you.