SAPUI5: How to create a Foreign Key link between two Tables in HANA

as the following titles states,

I do have Two Tables first one looks as the following:

COLUMN TABLE "FirstTable"(
    "exampleId" INTEGER CS_INT NOT NULL,
    "Name" VARCHAR(50) NOT NULL,
    PRIMARY KEY ("exampleId")

) UNLOAD PRIORITY 5 AUTO MERGE;

and Second Table, which should have the Primary Key of FirstTable as a Foreign Key, so that a link between those two tables is established.

SecondTable:

COLUMN TABLE "SecondTable"(
    "scndID" INTEGER CS_INT NOT NULL,
    "exampleId" INTEGER CS_INT NOT NULL,
    PRIMARY KEY ("scndID"),
    FOREIGN KEY("exampleId")

) UNLOAD PRIORITY 5 AUTO MERGE;

Issue: FOREIGN KEY doesn’t work / isn’t recognized.

Question: How to create a link between two tables with Foreign Keys ?

24 thoughts on “SAPUI5: How to create a Foreign Key link between two Tables in HANA”

  1. You were really close with the syntax you chose.
    All that is missing for the second table is to let HANA know what table the foreign key should belong to.

    You need to add the REFERENCES expression to the FOREIGN KEY expression.

    This is explained in the documentation here.

    create COLUMN TABLE "SecondTable"(
        "scndID" INTEGER NOT NULL,
        "exampleId" INTEGER  NOT NULL,
        PRIMARY KEY ("scndID"),
        FOREIGN KEY("exampleId") REFERENCES "FirstTable"
    );
    

    BTW: good choice to make all columns NOT NULL! It’s a common mistake to leave the default of NULLABLE and to then have to deal with NULLs all over the place.

    I would recommend to not include the column store data types (CS_INT) in your code – that’s just confusing and don’t add anything of value.
    Likewise, use NVARCHAR instead of VARCHAR unless there is a good reason for not doing it.

    Reply

Leave a Comment