[Firebird-devel] [FB-Tracker] Created: (CORE-3037) compound foreign key with on delete set null vs. not null columns

[Philip W. (JIRA) <tr...@fi...>]

compound foreign key with on delete set null vs. not null columns
-----------------------------------------------------------------

                 Key: CORE-3037
                 URL: http://tracker.firebirdsql.org/browse/CORE-3037
             Project: Firebird Core
          Issue Type: Improvement
          Components: Engine
         Environment: Tested on 2.1.3 SS Win32, but probably applies before and after.
            Reporter: Philip Williams
            Priority: Minor


If a compound foreign key is set to on-delete-set-null, it would be nice if it would not attempt to nullify not-null columns that may be part of other constraints; if even a single field is nullable, being set to null is sufficient to nullify the reference and allow the original deletion. If no fields are nullable, I agree that should be an error (for that matter, it could be an error at constraint-creation time.)

Nearest reports I could find (not exact match) were CORE-935 and CORE-77.

Test script below:

create table departments (
 dept_name varchar(30) not null,
 primary key (dept_name)
);

create table employees (
 dept_name varchar(30) not null,
 emp_name varchar(30) not null,
 boss_name varchar(30),
 primary key (dept_name, emp_name),
 foreign key (dept_name, boss_name) references employees (dept_name, emp_name) on update cascade on delete set null
);

commit;

insert into departments (dept_name) values ('IT');
insert into employees (dept_name, emp_name, boss_name) values ('IT', 'BigBoss', null);
insert into employees (dept_name, emp_name, boss_name) values ('IT', 'Underling', 'BigBoss');

commit;

delete from employees where dept_name = 'IT' and emp_name = 'BigBoss';
-- validation error for column DEPT_NAME, value "*** null ***"
rollback;

update employees set boss_name = null where dept_name = 'IT' and emp_name = 'Underling';
-- no error
rollback;

Yes, the above could be achieved by repeating the dept_name column, but if it is a business rule that they always be the same, that means adding CHECK constraints and/or triggers to support the rule; the simpler solution (for the end-user, that is) is for the database to recognize that because a single field being NULL is enough to eliminate the relationship, it follows that CASCADE SET NULL should only require a single field to be NULLed to have done its job.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira