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,
--
John English
--
This email has been checked for viruses by AVG antivirus software.
www.avg.com
