Hello,
This is my first post to the mailing list and I am a SQL NEWBIE...
I am attempting to convert a Postgresql schema to derby for testing
and need suggestions on working around the lack of a 'SET DEFAULT'
referential constraint in derby. I am looking for suggestions on
how to set the foreign key to its default value on delete.
Contrived example:
Postgres DDL:
CREATE TABLE bar (
id INT NOT NULL UNIQUE PRIMARY KEY DEFAULT
NEXTVAL('bar_sequence'),
name VARCHAR(100) DEFAULT 'HUNH'
...
);
CREATE TABLE foo (
id INT NOT NULL UNIQUE PRIMARY KEY DEFAULT
NEXTVAL('foo_sequence'),
name VARCHAR(100) DEFAULT 'UGH',
count INT NOT NULL DEFAULT '0'
bar_id INT NOT NULL DEFAULT '1' REFERENCES bar (id)
ON DELETE SET DEFAULT
ON UPDATE CASCADE
...
);
Derby DDL:
CREATE TABLE foo (
id INT NOT NULL UNIQUE PRIMARY KEY GENERATED ALWAYS AS IDENTY
(START WITH 1, INCREMENT BY 1),
name VARCHAR(100) DEFAULT 'UGH',
count INT NOT NULL DEFAULT '0',
bar_id INT NOT NULL DEFAULT '1' CONSTRAINT bar_fk
REFERENCES bar (id) ON DELETE ?????? <- don't know what to do
ON UPDATE CASCADE
...
);
--
One is what One does.
Mark W. Davis
