Help Center/ GaussDB(DWS)/ Tool Guide/ DSC/ SQL Syntax Migration Reference/ Teradata Syntax Migration/ Schema Objects and Data Definition Language (DDL)/ Migrating Tables/ CONSTRAINT
Updated on 2024-07-19 GMT+08:00
View PDF
Share

CONSTRAINT

A table CONSTRAINT is applied to multiple columns. Migration tool supports the following constraints:

  • CHECK constraint: supported by GaussDB T, GaussDB A, and GaussDB(DWS).
  • REFERENCES constraint / FOREIGN KEY: migration currently NOT supported by tool.
  • PRIMARY KEY constraint: migration supported by tool.
  • UNIQUE constraint: migration supported by tool.

Input: CREATE TABLE with CONSTRAINT

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
 
CREATE SET TABLE DP_SEDW.T_170UT_HOLDER_ACCT, NO FALLBACK, 
   NO BEFORE JOURNAL, NO AFTER JOURNAL 
 ( BUSINESSDATE   VARCHAR(10)                                            
 , SOURCESYSTEM   VARCHAR(5)                                             
 , UPLOADCODE     VARCHAR(1)                                             
 , HOLDER_NO      VARCHAR(7)  NOT NULL  
 , POSTAL_ADD_4   VARCHAR(40)                                                                                
 , EPF_IND         CHAR(1)  
 , CONSTRAINT uq_t_170ut_hldr UNIQUE ( SOURCESYSTEM, UPLOADCODE, HOLDER_NO )                                                                                       
      ) PRIMARY INDEX ( HOLDER_NO, SOURCESYSTEM ) ;

Output:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
 
CREATE TABLE DP_SEDW.T_170UT_HOLDER_ACCT 
   ( BUSINESSDATE      VARCHAR( 10 )
   , SOURCESYSTEM      VARCHAR( 5 )
   , UPLOADCODE        VARCHAR( 1 )
   , HOLDER_NO         VARCHAR( 7 )   NOT NULL
   , POSTAL_ADD_4      VARCHAR( 40 )
   , EPF_IND           CHAR( 1 )
   , CONSTRAINT uq_t_170ut_hldr UNIQUE ( SOURCESYSTEM, UPLOADCODE, HOLDER_NO )       
           )
DISTRIBUTE BY HASH ( HOLDER_NO, SOURCESYSTEM );

Input:

After table creation, CONSTRAINT can be added to a table column to put some restriction at column level by using ALTER statement.

1
2
3
4
 
CREATE TABLE GCC_PLAN.T1033 ( ROLLOUT_PLAN_LINE_ID NUMBER NOT NULL,
                                                            UDF_FIELD_VALUE_ID NUMBER NOT NULL) ;
ALTER TABLE GCC_PLAN.T1033 
ADD CONSTRAINT UDF_FIELD_VALUE_ID_PK UNIQUE (UDF_FIELD_VALUE_ID) ;

Output:

1
2
3
4
 
CREATE TABLE GCC_PLAN.T1033 ( ROLLOUT_PLAN_LINE_ID NUMBER NOT NULL,
                                                            UDF_FIELD_VALUE_ID NUMBER NOT NULL,
                                                            CONSTRAINT UDF_FIELD_VALUE_ID_PK 
                                                            UNIQUE (UDF_FIELD_VALUE_ID) ;

Need to put CONSTRAINT creation syntax inside table creation script after all column declaration.

Parent topic: Migrating Tables