Archives of the TeradataForum

Message Posted: Fri, 19 Jun 2015 @ 22:37:47 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi Team,

Can you help me with the following issue:

     ALTER TABLE DB_T_DEV_CORE.FEAT
                    ADD CONSTRAINT VAL_TYPE_FEAT FOREIGN KEY (VAL_TYPE_CD)
     REFERENCES WITH NO CHECK OPTION DB_T_DEV_CORE.VAL_TYPE (VAL_TYPE_CD);

     ALTER TABLE Failed. 3976:  Incorrect referential constraint definition.

Table Def:

     CREATE MULTISET TABLE DB_T_DEV_CORE.VAL_TYPE ,NO FALLBACK ,
          NO BEFORE JOURNAL,
          NO AFTER JOURNAL,
          CHECKSUM = DEFAULT,
          DEFAULT MERGEBLOCKRATIO
          (
           VAL_TYPE_CD VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC ,
           VAL_TYPE_DESC VARCHAR(250) CHARACTER SET LATIN NOT CASESPECIFIC,
     CONSTRAINT UPI_VAL_TYPE PRIMARY KEY ( VAL_TYPE_CD ))
     PRIMARY INDEX ( VAL_TYPE_CD );



     CREATE MULTISET TABLE DB_T_DEV_CORE.FEAT ,NO FALLBACK ,
          NO BEFORE JOURNAL,
          NO AFTER JOURNAL,
          CHECKSUM = DEFAULT,
          DEFAULT MERGEBLOCKRATIO
          (
           FEAT_ID INTEGER NOT NULL,
           FEAT_SBTYPE_CD VARCHAR(50) CHARACTER SET LATIN UPPERCASE NOT CASESPECIFIC NOT NULL,
           FEAT_INSRNC_SBTYPE_CD VARCHAR(50) CHARACTER SET LATIN UPPERCASE NOT CASESPECIFIC,
           FEAT_CLASFCN_CD VARCHAR(50) CHARACTER SET LATIN UPPERCASE NOT CASESPECIFIC,
           FEAT_DESC VARCHAR(250) CHARACTER SET LATIN UPPERCASE NOT CASESPECIFIC,
           FEAT_NAME VARCHAR(100) CHARACTER SET LATIN UPPERCASE NOT CASESPECIFIC,
           COMN_FEAT_NAME VARCHAR(100) CHARACTER SET LATIN UPPERCASE NOT CASESPECIFIC,
           FEAT_LVL_SBTYPE_CNT INTEGER,
           INSRNC_CVGE_TYPE_CD VARCHAR(50) CHARACTER SET LATIN UPPERCASE NOT CASESPECIFIC,
           INSRNC_LOB_TYPE_CD VARCHAR(50) CHARACTER SET LATIN UPPERCASE NOT CASESPECIFIC,
           FEAT_DATA_TYPE_NAME VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,
           FEAT_COVERABLE_TYPE_TXT VARCHAR(1000) CHARACTER SET LATIN NOT CASESPECIFIC,
           PRCS_ID INTEGER NOT NULL,
           FEAT_DTL_MODL_TYPE_NAME VARCHAR(1000) CHARACTER SET LATIN UPPERCASE NOT CASESPECIFIC,
           FEAT_DTL_CD_NAME VARCHAR(1000) CHARACTER SET LATIN UPPERCASE NOT CASESPECIFIC,
           FEAT_DTL_VAL DECIMAL(20,4),
           FEAT_DTL_VAL_TYPE VARCHAR(100) CHARACTER SET LATIN UPPERCASE NOT CASESPECIFIC,
           FEAT_DTL_COL_NAME VARCHAR(1000) CHARACTER SET LATIN UPPERCASE NOT CASESPECIFIC,
           NK_SRC_PUBLC_ID VARCHAR(100) CHARACTER SET UNICODE NOT CASESPECIFIC,
           VAL_TYPE_CD VARCHAR(50) CHARACTER SET LATIN UPPERCASE NOT CASESPECIFIC,
     CONSTRAINT UPI_FEAT PRIMARY KEY ( FEAT_ID ),
     CONSTRAINT FEAT_INSRNC_SBTYPE_FEAT FOREIGN KEY ( FEAT_INSRNC_SBTYPE_CD )
     REFERENCES WITH NO CHECK OPTION DB_T_DEV_CORE.FEAT_INSRNC_SBTYPE (FEAT_INSRNC_SBTYPE_CD ),
     CONSTRAINT FEAT_SBTYPE_FEAT FOREIGN KEY ( FEAT_SBTYPE_CD ) REFERENCES WITH
     NO CHECK OPTION DB_T_DEV_CORE.FEAT_SBTYPE ( FEAT_SBTYPE_CD ),
     CONSTRAINT FEAT_CLASFCN_TYPE_FEAT FOREIGN KEY ( FEAT_CLASFCN_CD ) REFERENCES
     WITH NO CHECK OPTION DB_T_DEV_CORE.FEAT_CLASFCN_TYPE ( FEAT_CLASFCN_CD ),
     CONSTRAINT INSRNC_LOB_TYPE_FEAT FOREIGN KEY ( INSRNC_LOB_TYPE_CD )
     REFERENCES WITH NO CHECK OPTION DB_T_DEV_CORE.INSRNC_LOB_TYPE (INSRNC_LOB_TYPE_CD ),
     CONSTRAINT INSRNC_CVGE_TYPE_FEAT FOREIGN KEY ( INSRNC_CVGE_TYPE_CD )
     REFERENCES WITH NO CHECK OPTION DB_T_DEV_CORE.INSRNC_CVGE_TYPE (
     INSRNC_CVGE_TYPE_CD ))
     PRIMARY INDEX ( FEAT_ID );

I have tried to recreate the VAL_TYPE_CD as Null able but didn't help much.

Raghu Maram
Teradata DBA

	<Prev	Next>		<<First	<Prev	Next>	Last>>