Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Thu, 26 Jun 2014 @ 08:20:28 GMT


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


Subj:   How to modify DDL when UPI is on IDENTITY COLUMN & RI imposed
 
From:   Bodla, Sravan Kumar

Hi Forum,

I have a table created as SET table using IDENTITY column as UPI and it has data in it.

Now, the user wants us to change it to MULTISET, but, as the PI is on Identity column how can I do this? (I am unable to create the copy of that table using Create table as .. (CREATE TABLE Failed. 5788: CREATE TABLE AS currently does not support Identity Columns. ), even at least to test any scenario or logic on the actual table.

The major concern here is this table is having RI constraint imposed on the UPI column and needs to have the same values after modifying.

Table Structure:

     CREATE SET TABLE P_INTERNAL_ORGANIZATION.SLS_AR ,NO FALLBACK ,
          NO BEFORE JOURNAL,
          NO AFTER JOURNAL,
          CHECKSUM = DEFAULT,
          DEFAULT MERGEBLOCKRATIO
          (
           SLS_AR_ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY
                (START WITH 1
                 INCREMENT BY 1
                 MINVALUE -2147483647
                 MAXVALUE 2147483647
                 NO CYCLE),
           Col2 VARCHAR(128) CHARACTER SET UNICODE NOT CASESPECIFIC,
           Col3  VARCHAR(128) CHARACTER SET UNICODE NOT CASESPECIFIC,
           Col4 DATE FORMAT 'yyyy-mm-dd',
           Col5_PR DATE FORMAT 'yyyy-mm-dd', CONSTRAINT UI_DVS_DSTRBT_CHNN_SL_ORGNZT
           UNIQUE ( DSTRBTN_CHNNL ,DVSN ,SLS_ORGNZTN ), CONSTRAINT      FK2_RPRTNG_ORGNZTN FOREIGN KEY ( RPRTNG_ORGNZTN )
           REFERENCES P_INTERNAL_ORGANIZATION.RPRTNG_ORGNZTN ( INTRNL_ORGNZTN_ID ))
           UNIQUE PRIMARY INDEX      PI_SLS_AR ( SLS_AR_ID ) INDEX SI_ACTV ( ACTV );

Your assistance on this would be highly appreciated.

Thanks in advance!


Thanks,

Sravan.

SRAVAN KUMAR BODLA
GCC International - Technical Consultant



     
  <Prev Next>  
<<First
<Prev
Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023