Referential Constraint

Description

Foreign Key Constraints are migrated via ALTER TABLE statements in order to remove dependencies at the table creation time and therefore facilitate database deployment.

Click here to navigate to the IBM DB2 docs page for this syntax.

Grammar Syntax

Sample Source Patterns

IBM DB2

CREATE TABLE T2(
    COL1 VARCHAR(1),
    CONSTRAINT FKCOL1 FOREIGN KEY (COL1) REFERENCES T1,
    CONSTRAINT FKCOL2 FOREIGN KEY (COL1) REFERENCES T1(COL1),
    CONSTRAINT FKCOL3 FOREIGN KEY (COL1) REFERENCES T1(COL1) ON DELETE CASCADE ON UPDATE NO ACTION,
    CONSTRAINT FKCOL4 FOREIGN KEY (COL1) REFERENCES T1(COL1) ENFORCED DISABLE QUERY OPTIMIZATION,
    FOREIGN KEY (COL1) REFERENCES T1
);

Snowflake

CREATE OR REPLACE TABLE PUBLIC.T2 (
COL1 VARCHAR(1)
);
ALTER TABLE PUBLIC.T2 ADD
CONSTRAINT FKCOL1 FOREIGN KEY (COL1) REFERENCES PUBLIC.T1 ;
ALTER TABLE PUBLIC.T2 ADD
CONSTRAINT FKCOL2 FOREIGN KEY (COL1) REFERENCES PUBLIC.T1 (COL1) ;
ALTER TABLE PUBLIC.T2 ADD
CONSTRAINT FKCOL3 FOREIGN KEY (COL1) REFERENCES PUBLIC.T1 (COL1) ON DELETE CASCADE ON UPDATE NO ACTION;
ALTER TABLE PUBLIC.T2 ADD
CONSTRAINT FKCOL4 FOREIGN KEY (COL1) REFERENCES PUBLIC.T1 (COL1) ENFORCED                                                                 
-- ** MSC-WARNING - MSCEWI1002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  **
--                                                                          DISABLE QUERY OPTIMIZATION
;
ALTER TABLE PUBLIC.T2 ADD CONSTRAINT T2_COL1_PUBLIC_T1_COL1
FOREIGN KEY (COL1) REFERENCES PUBLIC.T1 ;

Related EWIs

1. MSCEWI1002: Removed next statement, not applicable in Snowflake.