Truncate not supported for tables with "disabled" foreign key constraints pointing to them

[lbreddemann]

When I saw in the documentation that HANA 2 supports the disabling of foreign key constraints, I was happy.
Finally, I thought, HANA could do what other SQL DBMS were able to do since, well, forever.

While HANA's choice to split this "deactivation" of the constraint into two options (one for existing records and another one for newly changed/created records) can be helpful, it also creates more effort when working in a typical data loading/staging scenario.
In such a situation one typically wants to truncate a table and fill it with new data without being interrupted by FK constraint validation errors, that will eventually be fixed later in the loading process.

Anyhow, I tried to use this new feature only to be presented with the error message:

SQL Error [7] [HY000]: SAP DBTech JDBC: [7]: feature not supported: truncate on tables with foreign key constraints

That's rather disappointing.

So, this question is to inquire what SAP's plans around this are.

This has worked so nicely the last time, so maybe taesuk.son and/or denys.kempen can help with this again?

Mind you, a simple DELETE FROM <TABLE>; works without any issues - but that's not what we want when we use a TRUNCATE, isn't it?

The following are the reproduction steps:

/*
 * truncating a table does not work even with all FK constraints pointing to it 
 * "disabled"
 * */

SELECT current_date, * FROM m_database;

-- CURRENT_DATE|SYSTEM_ID|DATABASE_NAME|HOST   |START_TIME         |VERSION               |USAGE      |
-- ------------|---------|-------------|-------|-------------------|----------------------|-----------|
--   2020-09-24|HXE      |HXE          |hxehost|2020-09-24 12:57:24|2.00.045.00.1575639312|DEVELOPMENT|

CREATE SCHEMA FK;
SET SCHEMA fk;
CREATE TABLE myusers (userid bigint PRIMARY KEY
                    , username nvarchar(400) NOT NULL);

CREATE TABLE orders (ORDERid bigint PRIMARY KEY
                    , createdby bigint
                    , updatedby bigint);

ALTER TABLE orders ADD CONSTRAINT "FK_ORDERS_CREATEDBY" 
                       FOREIGN KEY (createdby) 
                       REFERENCES myusers (USERID);

ALTER TABLE orders ADD CONSTRAINT "FK_ORDERS_UPDATEDBY" 
                       FOREIGN KEY (updatedby) 
                       REFERENCES myusers (USERID);
                 
INSERT INTO myusers VALUES (1, 'Lars');
INSERT INTO orders VALUES (10, 1, 1);

SELECT * FROM orders;

truncate TABLE orders;
--this works
truncate TABLE myusers;
--SQL Error [7] [HY000]: SAP DBTech JDBC: [7]: feature not supported: truncate on tables with foreign key constraints

ALTER TABLE orders ALTER CONSTRAINT fk_orders_createdby NOT enforced NOT validated;
ALTER TABLE orders ALTER CONSTRAINT fk_orders_updatedby NOT enforced NOT validated;

truncate TABLE myusers;
--SQL Error [7] [HY000]: SAP DBTech JDBC: [7]: feature not supported: truncate on tables with foreign key constraints

DELETE FROM myusers;
-- NO problemo, users gone.

Cheers,

Lars

[dvankempen]

Mr Holmes,

Interesting findings. No doubt my colleague will be qualified to comment on this but this might require a little patience. I will follow your Q and keep an eye on it.

All is well, down under?

[Taesuk]

Hi Lars,

Thank you for pointing this out. I've researched other databases and it seems that this is supported by most vendors but some only supports this by DELETE only.

Do you mind adding this request to our customer influence https://influence.sap.com/sap/ino/#campaign/902 if you feel that it is an important feature that needs to be supported?

[lbreddemann]

I appreciate the reply.

When I try using the link you provided, I get to the Influence site and then an error "Object does not exist or access is denied".

Is my user missing any permissions? Note, I don't own an S-user account that I could use for this.

[lbreddemann]

Until this feature is supported, it would be great to have the documentation reflect that TRUNCATE is not supported - otherwise everybody else will have to find this out by means of the error message, which is not very user/developer friendly.