Archives of the TeradataForum

Message Posted: Mon, 08 Sep 2008 @ 13:58:33 GMT

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

Hi Jim,

How to Drop identity property from a table. Other tables are referring by this table identity column.

Ex:

     Present table:
     -------------
     CREATE SET TABLE EFM_Audit
          (
           AUDIT_ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY
                (START WITH 1
                 INCREMENT BY 1
                 MINVALUE -2147483647
                 MAXVALUE 2147483647
                 NO CYCLE),
           PARENT_AUDIT_ID INTEGER,
           EVENT_NAME VARCHAR(25),
           MESSAGE_TYPE VARCHAR(25),
           ACTION VARCHAR(50),
           EFFECTIVE_DT TIMESTAMP(0) NOT NULL,
           UPDATE_COMMAND VARCHAR(2048),
           WORKSHEET_PK VARCHAR(100),
           ACTION_IP_ADDRESS VARCHAR(25),
           UID_PK VARCHAR(50) NOT NULL,
           JOB_ID INTEGER,
           PERIOD_ID SMALLINT,
           VERSION_ID INTEGER)
     UNIQUE PRIMARY INDEX PK_EFM_AUDIT ( AUDIT_ID );

After drop the identity property Required Syntax:

     ------------------------------------------------
     CREATE SET TABLE EFM_Audit
     (
     AUDIT_ID INTEGER NOT NULL,
     PARENT_AUDIT_ID INTEGER,
     EVENT_NAME VARCHAR(25),
     MESSAGE_TYPE VARCHAR(25),
     ACTION VARCHAR(50),
     EFFECTIVE_DT TIMESTAMP(0) NOT NULL,
     UPDATE_COMMAND VARCHAR(2048),
     WORKSHEET_PK VARCHAR(100),
     ACTION_IP_ADDRESS VARCHAR(25),
     UID_PK VARCHAR(50) NOT NULL,
     JOB_ID INTEGER,
     PERIOD_ID SMALLINT,
     VERSION_ID INTEGER)
     UNIQUE PRIMARY INDEX PK_EFM_AUDIT ( AUDIT_ID );

Thanks,

Kishore KK

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