Nullable Column with Constraint Error on Insert

[Former Member]

I have a table which has a nullable column (ie, Null is allowed), but which also has a foreign key constraint. During an insert, I am getting an error when I leave out the fourth column, which I presume would make the column null:

Could not execute 'INSERT INTO ATTRIBUTE_VALUE (ATTR_VALUE_KEY, ATTR_KEY, VALUE) values (30,1,'VALUE 30-1')' in 96 ms 467 µs . SAP DBTech JDBC: [461]: row locked by other transaction:

However, if I were to add the a key for the fourth column, DATA_SOURCE_SYSTEM_KEY, referencing a valid row, the insert will work.

Why would this happen?

[former_member184768]

Hi Kevin,

Can you please try with the statement as

INSERT INTO ATTRIBUTE_VALUE (ATTR_VALUE_KEY, ATTR_KEY, VALUE, DATA_SOURCE_SYSTEM_KEY) values ..

but please provide the value NULL for the column DATA_SOURCE_SYSTEM_KEY. This will confirm if the issue is related to NULL values or referential integrity constraints.

Regards,

Ravi

[Former Member]

Ravi,

I tried your idea with "NULL" and "null",

INSERT INTO ATTRIBUTE_VALUE (ATTR_VALUE_KEY, ATTR_KEY, VALUE, DATA_SOURCE_SYSTEM_KEY) values (57,1,'VALUE 57-1',NULL);

but both still yielded:

Could not execute 'INSERT INTO ATTRIBUTE_VALUE (ATTR_VALUE_KEY, ATTR_KEY, VALUE, DATA_SOURCE_SYSTEM_KEY) values ...' in 43 ms 610 µs . SAP DBTech JDBC: [461]: row locked by other transaction: 

-- Kevin

[lbreddemann]

Hi Kevin,

the current implementation of the foreign key constraint is pretty strict.

Once created, you can only insert tuples that comply to the constraint.

This check is done upon insert/update time on the referencing table.

The only way to get NULL values into the referencing column is to set the UPDATE rule so that when the referenced value is deleted/changed in the referenced table, the referencing column values are set to NULL.

Obviously this would produce a kind of  'orphan' data in your data model and you'd have to deal with that manually.

So, even if you haven't specified the NOT NULL constraint, the foreign key constraint requires to insert/update only non-NULL values.

- Lars

[former_member184768]

Hi Lars / Kevin,

I think there is some bug. I tried the following statements and still got the same error.

drop table t_dept;
drop table t_emp;
create column table t_dept (dept_id tinyint primary key);
create column table t_emp (emp_id tinyint primary key, ename varchar(5), dept_id tinyint);
ALTER TABLE t_emp ADD CONSTRAINT dept_fk FOREIGN KEY (dept_id) REFERENCES t_dept(dept_id) on update set null;
insert into t_dept values (1);
insert into t_dept values (2);
insert into t_dept values (3);
select * from t_dept;
insert into t_emp (emp_id, ename) values (1, 'aaa');
insert into t_emp (emp_id, ename, dept_id) values (2, 'bbb', null);
insert into t_emp (emp_id, ename, dept_id) values (3, 'ccc', NULL);
insert into t_emp (emp_id, ename, dept_id) values (4, 'ddd', 1);
--truncate table t_emp;
--alter table t_emp drop constraint dept_fk;
select * from REFERENTIAL_CONSTRAINTS where table_name in ('T_EMP', 'T_DEPT');

Hi , The SET UPDATE rule should be applicable for the referenced table. In my case the T_DEPT table. Which means any change to the data value in the DEPT table will have the impact on EMP table set by the SET rule. This should not prevent me from entering the data in the EMP table. I understand that there would be a scan for value in DEPT table when I try to enter data in EMP table, but REFERENTIAL constraint should allow me to enter NULL values.

Surprisingly, if I don't set the reference key constraint first and insert NULL Values for the DEPT_ID in EMP table and then put the reference key constraint, it allows me to do so. Which means the reference key constraint can be implemented later after the data is added to EMP table with null values.

So now I am not very sure how this should work.

Regards,

Ravi

[lbreddemann]

Hi Ravi,

that's not quite right.

The referential key constraint basically states:

"You cannot enter any data in this column that is not found in the referenced column!".

Since it is required that the referenced column(s) constitute the primary key of the reference table, the valid values for the column can never contain NULLs.

A primary key always implicitly constitutes a NOT NULL constraint.

And so does the FOREIGN KEY constraint by only allowing primary keys to be referenced.

The 'loop holes' in the current implementation are obvious:

- existing data is not evaluated when you create the FK constraint

- via the SET UPDATE rule it's possible to create NULL entries in the referencing table.

Selectively allowing non-compliant tuples while denying to enter new non-compliant tuples (e.g. have a deferred or non-validated ref. constraint) is a feature that can be found in other DBMS and is quite handy for data loading scenarios.

However, at the moment HANA supports just the static behavior I just explained.

- Lars

[former_member184768]

Hi Lars,

Thanks for the clarification. But the behavior in any other database (like Oracle or SQL Server) is not inline with this. Typically it should allow you to enter data with NULL value in the referencing table. I mean, in my case, it should allow me to enter an Employee with DEPT ID as null as an employee which is not assigned to any department. That's why I can define DEPT_ID in EMP table as nullable and still referencing DEPT (DEPT_ID).

My understanding is the SET UPDATE rule is applicable to DEPT table and not on EMP table. That is, if I change a value from DEPT table from 1 to 10 for dept id 1 and if there exist Employees with DEPT ID assigned as 1, then depending upon the UPDATE rule, either the update on DEPT should not be allowed (RESTRICT) or all the Employee records should be UPDATE to NULL (SET NULL).

Also in other databases, foreign key can be referencing to the PRIMARY KEY or UNIQUE KEY and Unique key allows null.

So my understanding is HANA should allow NULL in DEPT ID in EMP table, just like any other database. But as you mentioned, it is not the same behavior in HANA.

Regards,

Ravi

[lbreddemann]

Hi Ravi,

agreed - other DBMS do allow the NULL insertion.

I'll see whether I can get hold of some SQL standard document and check whether it's specified how a NULL insert should be handled.

From my point of view, this primarily leads to more issues, as we allow a breach in our constraint model by allowing tuples for which the ref-constraint cannot be satisfied.

However, I do acknowledge of course that this feature allows for more flexibility in terms of constraint design and data loading.

For now I'd say: this is the way HANA works right now and we'll see if there is interest to have this feature extended.

- Lars

[former_member184768]

Hi Lars,

Was just checking the Foreign key definition on the web and as per the definition on Wiki, it should allow NULL values.

For referential integrity to hold in a relational database, any field in a table that is declared a foreign key can contain either a null value, or only values from a parent table's primary key or a candidate key.^[2]

Regards,

Ravi