Skip to Content
avatar image
Former Member

How to add foreign keys in SQL 92 Scripting

Hello

I was trying to manipulate different tables and estabilsh foreign key relatioinships between them. Then script I used is

ALTER TABLE <Schema>."CAT_DIM"  ADD (
FOREIGN KEY (SUB_DEPT_SR_KEY,DEPT_SR_KEY) REFERENCES
SUB_DEPT_DIM(SUB_DEPT_SR_KEY,DEPT_SR_KEY)); And was throwing error:

Could not execute 'ALTER TABLE "schema"."CAT_DIM" ADD ( FOREIGN KEY (SUB_DEPT_SR_KEY,DEPT_SR_KEY) REFERENCES ...'

SAP DBTech JDBC: [257] (at 50): sql syntax error: incorrect syntax near "KEY": line 2 col 16 (at pos 50)

Is it possible to alter the table adding foreign key in SQL92 syntax or do i need to establish the relationship while framing CREATE TABLE stmt?

Regards,

AN

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Mar 20, 2012 at 05:19 AM

    Hi Anish,

    The SQL script is used in the SAP HANA is different form Standard SQL.

    For more details, Please find the below link:

    http://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/20514d3c-f4e1-2e10-55a5-dc9a3efc0639?QuickLink=index&overridelayout=true&52591874613119

    If you want to alter the relationship between the tables then go for attribute or analytical view. In this you can join the table through join type "referential".

    Hope it is useful.

    Regards,Neha

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Mar 20, 2012 at 04:55 PM

    Hi,

      I dont think the foreign key relationship is possible to define the way you mention. For all possible 'alter table' options, please refer to: http://help.sap.com/hana_appliance#section5 --> SQL Reference Guide

      You can then get the desired behavior by creating the attribute views, analytical view or by using CE funtions like CE_JOIN,CE_CALC etc. , or by writing simple SQL using 'INNER JOIN', 'OUTER JOIN' etc.

    Regards, Rahul

    Add comment
    10|10000 characters needed characters exceeded

  • Mar 22, 2012 at 05:01 PM

    Hi Anish,

    plain and simple: as of today HANA does not support foreign key constraints.

    The only supported constraint types are primary key constraint and not null constraints.

    And yes, the consequence of this is that you basically cannot create a relational database that is guaranteed to be consistent.

    On the other side: SAP did not use FKs for the NetWeaver suite at all, since the application servers handled the consistency checking.

    regards,

    Lars

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi Lars,

      I know this post is pretty old, but it is in context, so Im posting my question here.

      HANA now supports foriegn keys. However, like you mentioned, the impact on performance is quite severe. At times the runtime of a simple INSERT increases 10 fold with the addition of a foreign key constraint. Is there anything we can do to minimise this? Or is it that we should instead build our own checks and spare HANA the scourge of of these constraints?

      Regards,

      Nehal.