db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mark Davis <...@netvisage.net>
Subject Referential Constraint dilema
Date Thu, 30 Sep 2004 00:54:54 GMT
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


Mime
View raw message