SSC-FDM-OR0014

Foreign key data type mismatch.

Description

This error happens when there is a mismatch in a foreign key data type.

Example Code

Input Code:

IN -> Oracle_01.sql

CREATE TABLE "MyDb"."MyTable"
(
    "COL1" NUMBER,
    CONSTRAINT "PK" PRIMARY KEY ("COL1")
);

CREATE TABLE "MyDb"."MyTable1"
(   
    "COL1" NUMBER(*,0),
    CONSTRAINT "FK1" FOREIGN KEY ("COL1") REFERENCES "MyDb"."MyTable" ("COL1")
);

Output Code:

OUT -> Oracle_01.sql

CREATE OR REPLACE TABLE "MyDb"."MyTable"
    (
        "COL1" NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/,
        CONSTRAINT "PK" PRIMARY KEY ("COL1")
    )
    COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
    ;

    CREATE OR REPLACE TABLE "MyDb"."MyTable1"
    (
        "COL1" NUMBER(38) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/
    )
    COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
    ;

    ALTER TABLE "MyDb"."MyTable1"
    ADD
    --** SSC-FDM-OR0014 - FOREIGN KEY DATA TYPE MISMATCH **
    CONSTRAINT "FK1" FOREIGN KEY ("COL1") REFERENCES "MyDb"."MyTable" ("COL1");

Note that "MyDb"."MyTable1".COL1 and "MyDb"."MyTable".COL1 are of different types and the ERROR is displayed.

Recommendations

• If there are cycles in the data hierarchy, you can review this article to deal with them.

• If you need more support, you can email us at [e-mail]