Foreign Key in SAP HANA

v  A foreign key is a column (or columns) that references a column (most often the primary key) of another table. The purpose of the foreign key is to ensure referential integrity of the data. In other words, only values that are supposed to appear in the database are permitted.

Why should I use foreign keys?

v  The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables. The FOREIGN KEY constraint also prevents invalid data from being inserted into the foreign key column, because it has to be one of the values contained in the table it points to.

Can a foreign key have a null value?

v  Any number of rows in the child table can reference the same parent key value, so this model establishes a one-to-many relationship between the parent and foreign keys. However, each row in the child table must have a reference to a parent key value; the absence of a value (a null) in the foreign key is not allowed.

What is the difference between a primary key and a foreign key?

v  A primary key is a column or a set of columns that uniquely identify a row in a table. ... Usually, a foreign key is in a table that is different from the table whose primary key it is required to match. A table can have multiple foreign keys. The primary key cannot accept null values.

v  A foreign key must reference either the primary key or a unique key of the parent table. If the primary key has multiple columns, the foreign key must have the same number and order of columns. Therefore the foreign key references a unique row in the parent table; there can be no duplicates.

Examples:

Here I already create a "KABIL_PRACTICE"."PRODUCT_PRIMARY_KEY" Table.

See: How to create Primary Key in SAP HANA

Now I’m creating the another table with foreign Key.

Code:

CREATE column TABLE "KABIL_PRACTICE"."SALES_FOREIGN"

( 

"Order_code" integer, 

"cust_name" varchar(25),

"Prod_Id"  integer  NOT NULL,

"Prod_Name" varchar(25), 

"Quantity" integer Not Null,

"Sales_Price" integer, 

FOREIGN KEY("Prod_Id") 

REFERENCES "KABIL_PRACTICE"."PRODUCT_PRIMARY_KEY" ("Prod_Id") 

) ;

And insert some records into the table.

Code:

insert into "KABIL_PRACTICE"."SALES_FOREIGN" values (1,'kabil',1,'fan',10,15000);

Result:

Sales Table

And now I try to insert another record into the table.

Code:

insert into "KABIL_PRACTICE"."SALES_FOREIGN" values (2,'arasan',2,'chair',5,7500);

If I try to insert another record which is not present in the product table it throws an error like this,

Error

Thanks for Visiting my blog...

Please subscribe for updates and Share your comments...