How to add foreign keys in SQL 92 Scripting

[Former Member]

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

[lbreddemann]

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

[Former Member]

Any idea if the foreign keys will be added to HANA?

[lbreddemann]

I'm not aware of any plans to have this added.

Personally, I think that this feature is not as important to our product/solution portfolio.

Just as complex constraints (as in "business terms"-constraints) aren't important to have on DB level.

But hey - who knows, I could be soooo wrong about this .

Cheers, Lars

[Former Member]

I had a discussion about it yesterday with a colleague and I agree with you of while talking about HANA as a data warehouse.

But since there is intention of using HANA as a main database of a system this feature seems to be interesting.

[former_member182313]

Hi Lars,

As you can see from the screenshot below, I guess moving everything(as much as possible) to DB layer is(or will be?) the trend. I guess foreign key constraints is something easily implementable in the DB layer.

And this diagram demonstrates why SAP NW implemented those constraints in the application layer.

New DB, new thinking, new approach(aka Future approach) is required imo.

Best regards,

Bala

[lbreddemann]

Hello Marcos,

generally talking you're of course right.

But for huge transaction processing systems like NetWeaver, often constraints aren't implemented on DB level for performance reasons.

For example in NetWeaver we don't have FK-Contraints at all. (this is also related to the UPDATER process we employ).

So, for all SAP systems, the contraints won't be required - which is the reason for me to believe that we won't see that feature rather soon.

cheers, Lars

[lbreddemann]

Hello Bala,

the major point in not implementing foreign key constraints in the DB-layer is our (NetWeavers) way to deal with "Business-Transactions" instead of "DB-Transactions".

When we change data, it's quite possible that it exists in an inconsistent state (seen from the db-constraints) on the database for some time.

The reason to have it in the application layer was not that this constraint checking wasn't doable in the former DBMS but just the UPDATER approach.

And while I appreciate your "new thinking" re-stressing, I've to say:

a) this is not so new at all

and

b) the keypoint of this slide is to move data intensive work to the database.

Constraint-handling is typically not so data intensive.

So, as long as we don't integrate a business transaction concept into the database level, we won't see this on the DB level.

Cheers,
Lars

[former_member182313]

Hi Lars,

Thanks for your insight. We're on the same page on "data intensiveness".

Best regards,

Bala

[esjewett]

Hi Lars and Bala,

Just to add to this, and expand a bit on the recent discussion of BW and HANA, where would foreign key constraints be useful? I think there are a few areas in BW:

1. On technical join-IDs between system tables, like between SID tables (of master data objects), dimension tables, and fact tables in cubes.
2. On tables that have semantic links for technical reasons in the system, like between the central request table in the system and the change log table of a DSO.
3. On master-data/master-data semantic links in the system, like InfoObject IDs and attributes IDs of other InfoObjects.
4. On master-data/transactional-data semantic links like InfoObject ID to the IDs in a column of a DSO.

But looking at each of these, the semantics of foreign key constraints only apply to #1, and even here they wouldn't be a good idea all the time. For example, when loading new transactional data, the semantics of BW say that I can allow loading master data values that don't exist in an InfoObject. The system will create these values in the InfoObject. But implementing this under foreign key constraints will cause performance issues because we will not be allowed to update the master data SID table and the cube dimension table in parallel processes. Instead we'll have to serially update the SID table and then update the dimension table. When you have a cube with 100 characteristics in it, this is the kind of process you really want to be able to parallelize

That said, lots of other applications do use foreign key constraints or rely on other aspects of the SQL spec. It would be great if there was more of a priority on HANA fully supporting SQL and DDL so that developers could have more confidence that their SQL is portable to HANA. This support was a promise that SAP made early on and then seems to have forgotten about

Cheers,

Ethan

[former_member182313]

Hi Ethan,

It seems like you're arguing for rewriting BW. I don't want to digress. Let us focus on the issue on hand.

I guess there is no disagreement on the usefulness of foreign key constraints. We can identify several more scenarios where FK constraints would be useful both in BW and non-BW systems. SAP chose to implement them in the application layer due to UPDATER approach(I assume Lars means asynchronous update process). And my opinion why they chose asynchronous approach was due to the response time issues when working with DB directly/synchronously. Assuming this is correct, I would believe they would want to move this to DB layer using Future Approach. And FK constraint is one option they could consider. However FK constraint is old thinking. May be in Future Approach they've a better option to implement this feature.

And I've one question on your "parallelism" statement. Not sure I understand that FK constraint would lead to performance issues. When FK constraint is in place, one wouldn't be able load transactional data before loading parent data(master), parallel or not. In case you're talking about trapping the FK error programmatically and then try to create master data using the current process, then the level of concurrency(aka parallelism) would be the same regardless of the method you use. What am I missing?

And I agree on portability of SQL from legacy DB to SAP-HANA. I know there are always going to be differences between two databases; is FK constraint one of them? If so, how to implement in SAP-HANA?

Thanks and best regards,

Bala

[lbreddemann]

Bala Prabahar wrote:
{...}
due to UPDATER approach(I assume Lars means asynchronous update process). And my opinion why they chose asynchronous approach was due to the response time issues when working with DB directly/synchronously. Assuming this is correct, I would believe they would want to move this to DB layer using Future Approach. And FK constraint is one option they could consider. However FK constraint is old thinking. May be in Future Approach they've a better option to implement this feature. 
{...}

Sorry Bala, but contraints in a data model are far from being "old thinking" and actually there is no "future approach" for this concern of data design.

Reading your reply I'm not too sure about how deep you digged into this topic, but the process of checking non-trivial constraints (e.g. constraints that rely on other tuples states) is a serialization point for all transaction processing systems. The very definition of most constraints is based on that they have to be true for every single tuple.

And this inherent serialization (not the response time of classic DBMS systems) is the reason to not implement the constraints on DB level. Also: todays DBMSs only support a rather limited set of possible constraints.

It's actually quite difficult to express somewhat complex business rules in terms of constraints.

If this stuff is interesting to you, make sure to check the work of Chris Date (e.g. here http://www.thethirdmanifesto.com/) and Toon Koppelaars (http://thehelsinkideclaration.blogspot.co.at/).

cheers,

Lars

[former_member182313]

Hi Lars,

Thanks for the links. I already browsed through Toon's blog. I agree in principle-didn't read the use case though:

Now beware though, and we give you this warning beforehand: using triggers to implement data integrity constraints, is by far not easy. In fact it's extremely complex (as we will detail in future posts). But to me that is no reason to not use triggers. And this use-case will not suffer from the automagic side-effects the other three use-cases had. So I disagree here when Tom Kyte says that even for this use-case 'thou shall not use triggers'.

I believe there is time and place for everything("Triggers considered harmful" considered harmful is an example. Triggers are useful in some situations"). I'm not suggesting the number and the complexity of constraints offered by the legacy databases would meet every business situation. If the benefits offered by the constraints is very close to nil, then this discussion is not useful. However based on Ethan's response & original question, it seems the community is interested in this feature.

I happened to read this

Asynchronous updating is useful when the response time from the transaction is critical, and the database updates themselves are so complex that they justify the extra system load of logging them in VBLOG. 

Serialization-imo- is just one aspect of any changes we make in the database. All DML statements suffer from this constraint, serialization. And this,to a certain extent, is handled by SAP via SAP's lock table. And whether done by DB or SAP, FK constraints will be handled serially. One advantage when done through the application is response time. How? If the table is buffered, then we don't need to make that extra read from the DB server.

the dialog work process will not wait for the update process to finish.

Serialization in SAP's LUW is handled by SAP's lock table so if the response time is not an issue, SAP could've chosen to synchronously update the database(Here update means any DML statement), right?

I don't know if I expressed my thoughts eloquently. If not, I'll think about writing(Read "opening a can of worms") a blog.

Best regards,

Bala

[lbreddemann]

Hello Bala,

to be up-front with you: I lost track here, now.

I still don't see how the "new thinking" of HANA should change anything about busines data design concerning constraints.

And I also don't see the point in adding feautures to the core database engine now, at a time where we don't even support arbitrary custom database solutions to be build on HANA.

regards,

Lars

[former_member182313]

Lars,

Last response is not about "new thinking"; it was about serialization vs response time.

Thanks for your time.

[henrique_pinto]

Well it's a few months later and now we do support (officially) non-SAP application layers to use HANA as a relational DB, according to SAP Note 1577128, in particular .NET and Java as stated in http://www.saphana.com/community/blogs/blog/2012/11/01/sap-hana-opens-sql-interfaces-for-custom-deve....

SPS5 is also supposed to be mainly focused on supporting transactional-related functionalities on HANA (as it was announced on TechEd).

And HANA still doesn't support foreign keys... 😕

It's going to be a bit tough to explain to our customers how to move their existing Java & .NET applications built on top of relational DBs, fore sure with primary & foreign keys, to HANA...

With this approach, either HANA will remain as a viable option just for SAP applications and/or as an analytic DB. It seems the development strategy and Vishal's and Bill's vision for HANA are a bit out of sync (or just delayed in time).

[lbreddemann]

Hi Henrique,

without being sarcastic, but this is a huge advancement!

This seemingly small change of policy in that SAP note actually opens up new areas in which HANA can be used as the fast analytic data base management system.

That can't - and of course it doesn't - mean, that we build a database in less then two years that is now not only way faster than all competition but also on par feature wise.

Many applications nowadays seem to be happy or at least OKish without properly implementing data consistency constraints. And as there is a huge pile of features eagerly requested by customers to be implemented or to become supported (just like the SAP note you mentioned), I figure that as soon as those customers demand FK constraints, it will become much more likely to see them in the product.

as always: just my personal 2ct on this topic.

[henrique_pinto]

Let's hope so. 😉

[lbreddemann]

Hi Henrique,

aaaaand wish granted !

With SPS5 foreign key constraints are available in SAP HANA!

Check it out here.

Cheers, Lars

[henrique_pinto]

LIKE!!!

[henrique_pinto]

Hi Lars,

apparently there is a little problem in the documentation.

For the ALTER_TABLE statement, apparently the documentation is ok.

Check the example:

ALTER TABLE S ADD CONSTRAINT FK FOREIGN KEY(FA) REFERENCES R(A) ON DELETE CASCADE;

However, for the CREATE_TABLE statement, the example syntax is a bit different:

CREATE TABLE F (FK INT, B NVARCHAR(10), FOREIGN KEY(FK) REFERENCES R ON UPDATE CASCADE);

In this example, it doesn't define which is the column in the referred table the FK should refer to! Could you please double check that?

[lbreddemann]

Henrique,

the syntax example doesn't cover every option, but simply shows one way to create FKs during table creation time.

You can easily provide column names to be referenced in the FOREIGN KEY clause like this:

CREATE TABLE F (FK INT, B NVARCHAR(10), FOREIGN KEY(FK) REFERENCES R (<column_name>) ON UPDATE CASCADE);

If you don't provide a column name (or a list of column names) then HANA will automatically create a matching between the foreign key column(s) and the primary key of the referenced table.

This is of course also described in the syntax documentation but I admit the BNF syntax is not exactly easy readable...

However - I would usually not go for the option to create the foreign key with table creation.

For starters, you may want to setup your FK constraints with proper names and this can only be done via the ALTER TABLE syntax.

BTW: if you want to review the existing FK constraints, check out the new system table referential_contraints !

cheers, Lars

[henrique_pinto]

Ahhhh, that's awesome!

I had checked "_SYS_STATISTICS"."CONSTRAINTS" and noticed that the FKs were not there.

I was exactly looking for the internal given name in order to drop just the constraint.

I was able to find my FK (with automatic given name _SYS_CONSTRAINT_809827_#0_#F0).

Good to know this new table contains the FKs. 😉

I just wonder if it wouldn't be possible to have them included in the existing CONSTRAINTS table (with maybe some new columns).

[lbreddemann]

🙂

Well, as I'm not the developer of any HANA feature but just a mere user, I really can't comment on where information are stored in our database catalog...

What I know though: it's always a good idea to have a look into the SYS/SYSTEM schema if some information are missing.

cheers, Lars

[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.

[Former Member]

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

[former_member182277]

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-dc9a3efc0...

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