Foreign Keys

Database
Enthusiast

Foreign Keys

 One of our data modelers came to me with the following scenario.  He is creating two tables and defining a foreign key on one that references the other.

CREATE  TABLE EDWSPACE.AMK_ACCT_ANC

(              ACCT_ID              INTEGER NOT NULL ,

                LOAD_TS              TIMESTAMP NOT NULL

)

                 UNIQUE PRIMARY INDEX ACCT_ANC_UPI

                 (                                             ACCT_ID

                 );

CREATE  TABLE EDWSPACE.AMK_ACCT_DTL

(              LOAD_TS              TIMESTAMP NOT NULL ,

                DW_END_TS            TIMESTAMP NULL ,

                DW_STRT_TS           TIMESTAMP NOT NULL ,

                SRC_END_TS           TIMESTAMP NULL ,

                SRC_LAST_UPDTD_TS    TIMESTAMP NULL ,

                SRC_STRT_TS          TIMESTAMP NULL ,

                ACCT_NBR             VARCHAR(20) NOT NULL ,

                ACCT_MODR_NBR        VARCHAR(20) NOT NULL ,

                ACCT_ID              INTEGER NOT NULL ,

                ACCT_CL_AMT          DECIMAL(15,3) NULL ,

                SRC_SYS_IDN_CD       VARCHAR(6) NOT NULL ,

                ACCT_TYPE_ID         INTEGER NOT NULL

)

                PRIMARY INDEX ACCT_DTL_NUPI

                 (                                             ACCT_ID

                 );

ALTER TABLE EDWSPACE.AMK_ACCT_DTL

                ADD PRIMARY KEY (ACCT_ID,DW_STRT_TS);

ALTER TABLE EDWSPACE.AMK_ACCT_DTL

                ADD  UNIQUE (ACCT_NBR,ACCT_MODR_NBR,DW_STRT_TS);

ALTER TABLE EDWSPACE.AMK_ACCT_DTL

                ADD FOREIGN KEY (ACCT_ID) REFERENCES EDWSPACE.AMK_ACCT_ANC (ACCT_ID);

When the DDL above is executed, an additional table is actually created:

 

CREATE SET TABLE EDWSpace.AMK_ACCT_ANC ,NO FALLBACK ,

NO BEFORE JOURNAL,

NO AFTER JOURNAL,

CHECKSUM = DEFAULT,

DEFAULT MERGEBLOCKRATIO

(

ACCT_ID INTEGER NOT NULL,

LOAD_TS TIMESTAMP(6) NOT NULL)

UNIQUE PRIMARY INDEX ACCT_ANC_UPI ( ACCT_ID );

CREATE SET TABLE EDWSpace.AMK_ACCT_DTL ,NO FALLBACK ,

NO BEFORE JOURNAL,

NO AFTER JOURNAL,

CHECKSUM = DEFAULT,

DEFAULT MERGEBLOCKRATIO

(

LOAD_TS TIMESTAMP(6) NOT NULL,

DW_END_TS TIMESTAMP(6),

DW_STRT_TS TIMESTAMP(6) NOT NULL,

SRC_END_TS TIMESTAMP(6),

SRC_LAST_UPDTD_TS TIMESTAMP(6),

SRC_STRT_TS TIMESTAMP(6),

ACCT_NBR VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

ACCT_MODR_NBR VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

ACCT_ID INTEGER NOT NULL,

ACCT_CL_AMT DECIMAL(15,3),

SRC_SYS_IDN_CD VARCHAR(6) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

ACCT_TYPE_ID INTEGER NOT NULL,

PRIMARY KEY ( DW_STRT_TS ,ACCT_ID ),

UNIQUE ( DW_STRT_TS ,ACCT_NBR ,ACCT_MODR_NBR ),

FOREIGN KEY ( ACCT_ID ) REFERENCES EDWSpace.AMK_ACCT_ANC ( ACCT_ID ))

PRIMARY INDEX ACCT_DTL_NUPI ( ACCT_ID );

CREATE SET TABLE EDWSpace.AMK_ACCT_DTL_0 ,NO FALLBACK ,

NO BEFORE JOURNAL,

NO AFTER JOURNAL,

CHECKSUM = DEFAULT,

DEFAULT MERGEBLOCKRATIO

(

LOAD_TS TIMESTAMP(6) NOT NULL,

DW_END_TS TIMESTAMP(6),

DW_STRT_TS TIMESTAMP(6) NOT NULL,

SRC_END_TS TIMESTAMP(6),

SRC_LAST_UPDTD_TS TIMESTAMP(6),

SRC_STRT_TS TIMESTAMP(6),

ACCT_NBR VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

ACCT_MODR_NBR VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

ACCT_ID INTEGER NOT NULL,

ACCT_CL_AMT DECIMAL(15,3),

SRC_SYS_IDN_CD VARCHAR(6) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

ACCT_TYPE_ID INTEGER NOT NULL)

PRIMARY INDEX ( ACCT_ID );

Can anyone give an explanation as to why this occurs?  I have not defined primary and foreign keys on a Teradata table, so I'm not familiar with why this works the way it does.

 

2 REPLIES
Enthusiast

Re: Foreign Keys

This is something about implementing hard RI and soft RI. I am trying to find some reference why Teradata is creating "_0" table but for the time being I can tell you if you apply soft RI, it won't create additional table.

ALTER TABLE EDWSPACE.AMK_ACCT_DTL
ADD FOREIGN KEY (ACCT_ID) REFERENCES WITH NO CHECK OPTION
EDWSPACE.AMK_ACCT_ANC (ACCT_ID) ;
Enthusiast

Re: Foreign Keys

Well, Teradata create error tables named with TableName_Incremental digit. But this is created as a result of invalid foreign key references. 

have you checked the table creation right after the DLL execution or after trying some loading?

If you have tried some loading, then is there any rows inserted into EDWSpace.AMK_ACCT_DTL_0

Khurram