Why does deferred foreign key constraint work, while doc says it shouldnt?

[patricebender]

Hello,

I have found the following paragraph in the SAP HANA Documentation:

DEFERRED - referential constraints are checked at commit time. If a referential constraint is violated, then the transaction is rolled back. Also, if <referential_triggered_action> is set to something other than RESTRICT, then the referential constraint check on the parent (referencing) table is not deferred and instead is checked immediately

So I would expect, that - once I set e.g. `ON DELETE CASCADE` on a foreign key constraint, the constraint `CHECK_TIME` is set to `INITIALLY_IMMEDIATE`, regardless of how it is defined in the DDL statement.

However, I have the following sql test script, where the `INITIALLY_DEFFERED` check time, works on my HANA Cloud (also tested on HANA 2 SP 6):

-- generated by cds-compiler version 4.3.3
CREATE TABLE Books (
  ID INTEGER NOT NULL,
  title NVARCHAR(5000),
  author_ID INTEGER NOT NULL,
  PRIMARY KEY(ID)
) WITH ASSOCIATIONS (
  MANY TO ONE JOIN Authors AS author ON (author.ID = author_ID)
);

-- generated by cds-compiler version 4.3.3
CREATE TABLE Authors (
  ID INTEGER NOT NULL,
  PRIMARY KEY(ID)
) WITH ASSOCIATIONS (
  MANY TO MANY JOIN Books AS books ON (books.author_ID = ID)
);

-- generated by cds-compiler version 4.3.3
ALTER TABLE Books
ADD CONSTRAINT c__Books_author
FOREIGN KEY(author_ID)
REFERENCES Authors(ID)
ON UPDATE RESTRICT
ON DELETE CASCADE
VALIDATED
ENFORCED
INITIALLY DEFERRED;

-- INSERT testdata
INSERT INTO Authors (ID) VALUES (1);
INSERT INTO Authors (ID) VALUES (2);
INSERT INTO Authors (ID) VALUES (3);

INSERT INTO Books (ID, title, author_ID) VALUES (2, 'Book 2', 1);
INSERT INTO Books (ID, title, author_ID) VALUES (3, 'Book 3', 2);
INSERT INTO Books (ID, title, author_ID) VALUES (1, 'Book 1', 1);
INSERT INTO Books (ID, title, author_ID) VALUES (4, 'Book 4', 3);

COMMIT;

-- INSERT Books with non-existing author

INSERT INTO Books (ID, title, author_ID) VALUES (1, 'Book 1', 42); -- everything OK

COMMIT; -- CONSTRAINT violation happen here

-- UPDATE Books to non-existing author

UPDATE BOOKS SET author_ID = 42 WHERE ID = 3 -- everything OK

COMMIT; -- CONSTRAINT violation happen here

Don't get me wrong, for me personally, SAP HANA behaves exactly like it should: Allowing me to insert records, even though they would violate the referential integrity as long as I don't `COMMIT` the transaction.

However, this seems odd, as the Documentation says otherwhise.

If an SAP HANA Expert can elaborate on this behavior, I would be very thankful.

Best Regards

Patrice