I haven't explored this in any depth, but you might be able to update
the foreign keys in a row-deleting procedure which runs with DEFINER
rights. This would require you to run with authentication and
authorization turned on. In normal operation, the application would be
run by an ordinary user with limited rights, NOT by the DBO. The
following script shows the basic mechanics of this. You might want to
read the section titled "Configuring fine-grained user authorization" in
the Derby Security Guide.
CONNECT 'jdbc:derby:/Users/rhillegas/derby/mainline/z/db1;create=true;user=dbo';
CALL syscs_util.syscs_create_user( 'DBO', 'dbo_password' );
-- shutdown in order to enable NATIVE authentication
CONNECT 'jdbc:derby:/Users/rhillegas/derby/mainline/z/db1;shutdown=true';
-- reboot database. it is now protected by NATIVE authentication
CONNECT
'jdbc:derby:/Users/rhillegas/derby/mainline/z/db1;user=dbo;password=dbo_password';
-- create an everyday user
CALL syscs_util.syscs_create_user( 'fred', 'fred_password' );
create table assemblies(id int primary key);
CREATE TABLE components (
id INTEGER GENERATED ALWAYS AS IDENTITY,
assembly INTEGER NOT NULL,
pred INTEGER DEFAULT NULL,
succ INTEGER DEFAULT NULL,
CONSTRAINT components_pk PRIMARY KEY (id),
CONSTRAINT components_1 FOREIGN KEY (assembly)
REFERENCES assemblies(id),
CONSTRAINT components_2 FOREIGN KEY (pred)
REFERENCES components(id),
CONSTRAINT components_3 FOREIGN KEY (succ)
REFERENCES components(id)
);
grant insert on assemblies to public;
grant update on assemblies to public;
grant delete on assemblies to public;
grant insert on components to public;
grant update on components to public;
create procedure deleteComponentRow(in rowID int)
language java parameter style java modifies sql data
external security definer
external name 'TestProcs.deleteComponentRow';
grant execute on procedure deleteComponentRow to public;
On 8/4/25 5:51 AM, John English wrote:
I have two tables, Assemblies and Components, and I want each
component to refer to the assembly it belongs to and I also want to
arrange the components in a sequence. The definition of Components I
tried looks like this:
CREATE TABLE components (
id INTEGER GENERATED ALWAYS AS IDENTITY,
assembly INTEGER NOT NULL,
pred INTEGER DEFAULT NULL,
succ INTEGER DEFAULT NULL,
CONSTRAINT components_pk PRIMARY KEY (id),
CONSTRAINT components_1 FOREIGN KEY (assembly)
REFERENCES assemblies(id)
ON DELETE CASCADE,
CONSTRAINT components_2 FOREIGN KEY (pred)
REFERENCES components(id)
ON DELETE SET NULL,
CONSTRAINT components_3 FOREIGN KEY (succ)
REFERENCES components(id)
ON DELETE SET NULL
);
The idea is that they components will form a doubly-linked list, where
the first component will have prev = null, and the last will have succ
= null, enabling me to insert, delete and rearrange components at will.
However, I get this error:
Foreign Key 'COMPONENTS_2' is invalid because 'The delete rule of
foreign key must be CASCADE. (The referential constraint is
self-referencing and the table is dependent in a relationship with a
delete rule of CASCADE.)'.
I want components to be deleted automatically when the assembly they
belong to is deleted, but I also want to ensure that pred/succ are
valid component IDs.
Can anyone suggest any other way to define this table so that I can
access components in an assembly sequentially and rearrange them at will?
TIA,
