Consider storing NO ACTION in the INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS columns when this is the declaration #4159
Description
H2 parses both standard SQL RESTRICT and NO ACTION referential actions for both update rules and delete rules, but doesn't really implement NO ACTION, mapping it to RESTRICT:
While this limitation is fine, of course, it's too bad that the information is erased from the INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS table:
create table foreign_key_rule_parent (
id int,
constraint pk_foreign_key_rule_parent primary key (id)
);
create table foreign_key_rule_child (
ref1 int,
ref2 int,
ref3 int,
ref4 int,
ref5 int,
ref6 int,
constraint fk_foreign_key_rule_child1 foreign key (ref1) references foreign_key_rule_parent (id),
constraint fk_foreign_key_rule_child2 foreign key (ref2) references foreign_key_rule_parent (id) on delete restrict on update restrict,
constraint fk_foreign_key_rule_child3 foreign key (ref3) references foreign_key_rule_parent (id) on delete no action on update no action,
constraint fk_foreign_key_rule_child4 foreign key (ref4) references foreign_key_rule_parent (id) on delete cascade on update cascade,
constraint fk_foreign_key_rule_child5 foreign key (ref5) references foreign_key_rule_parent (id) on delete set null on update set null,
constraint fk_foreign_key_rule_child6 foreign key (ref6) references foreign_key_rule_parent (id) on delete set default on update set default
);
SELECT constraint_name, update_rule, delete_rule FROM information_schema.referential_constraints;Producing:
|CONSTRAINT_NAME |UPDATE_RULE|DELETE_RULE|
|--------------------------|-----------|-----------|
|FK_FOREIGN_KEY_RULE_CHILD2|RESTRICT |RESTRICT |
|FK_FOREIGN_KEY_RULE_CHILD3|RESTRICT |RESTRICT |
|FK_FOREIGN_KEY_RULE_CHILD4|CASCADE |CASCADE |
|FK_FOREIGN_KEY_RULE_CHILD5|SET NULL |SET NULL |
|FK_FOREIGN_KEY_RULE_CHILD1|RESTRICT |RESTRICT |
|FK_FOREIGN_KEY_RULE_CHILD6|SET DEFAULT|SET DEFAULT|
I wonder if it would be possible to just maintain the information to preserve the intent?