on 09-24-2020 10:16 AM
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
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?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
User | Count |
---|---|
74 | |
9 | |
8 | |
7 | |
6 | |
6 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.