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
|
