Is RESTRICT the only available option for referential integrity constraints? If I run the attached to create a small database, I don't seem to get CASCADEs when I start to play around with mid_local in the Models table. If not, is this likely to
be implemented in future or can anyone suggest a simple work around ... or maybe I am just making a dumb error.

Thanks

Peter

++++++++++++++++++++++++++++++++++++++++

CREATE TABLE "Models"
 (
 "mid_local" INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 0, INCREMENT BY 1) NOT NULL
    DESCRIPTION 'The model id on the local machine.',
 "mid_global" INTEGER DEFAULT -1 NOT NULL
    DESCRIPTION 'The centrally allocated model id for all Extrapolate models that have passed through the Extrapolate central model library.',
 "model_name" VARCHAR(75) COLLATE "ANSI" DEFAULT '< Enter new model name >' NOT NULL
    DESCRIPTION 'The centrally allocated model id for all Extrapolate models that have passed through the Extrapolate central model library.',
 CONSTRAINT "primary_key" PRIMARY KEY ("mid_local")
 )
DESCRIPTION 'Contains the gemeral description of and metadata for each Extrapolate model.'
VERSION 1.00
INDEX PAGE SIZE 4096
BLOB BLOCK SIZE 512
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768


CREATE TABLE "Stakeholders"
 (
 "mid_local" INTEGER DEFAULT -1,
 "sid_local" INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 0, INCREMENT BY 1)
   DESCRIPTION 'id code for stakeholder group.',
 "stakeholder_name" VARCHAR(75) COLLATE "ANSI" DEFAULT ' Enter name of stakeholder group' NOT NULL
   DESCRIPTION 'Name of stakeholder group.',
 CONSTRAINT "primary_key" PRIMARY KEY ("mid_local","sid_local"),
 CONSTRAINT "unique_name" UNIQUE ("stakeholder_name"),
 CONSTRAINT "mid_local_integrity" FOREIGN KEY (mid_local) REFERENCES Models(mid_local) ON UPDATE CASCADE ON DELETE CASCADE  
 )
VERSION 1.00
INDEX PAGE SIZE 4096
BLOB BLOCK SIZE 512
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768

Peter Thorne <peter.thorne@stirlingthorne.co.uk> wrote in
news:2DDB07E1-6DF9-4AE9-BE87-A2A2E835058E@news.elevatesoft.com:

> Is RESTRICT the only available option for referential integrity
> constraints? If I run the attached to create a small database, I don't
> seem to get CASCADEs when I start to play around with mid_local in the
> Models table. If not, is this likely to be implemented in future or
> can anyone suggest a simple work around ... or maybe I am just making
> a dumb error.

Tim said in the beta newsgroup that for the moment only RESTRICT is
implemented, but he intends to develop a server-only version later which
would perhaps include CASCADE.

see <21CBAE9C-9F94-4A1D-B1A0-6E12C38D3B07@news.elevatesoft.com>

--
Chris
(XP-Pro + Delphi 7 Architect + DBISAM 4.25 build 3 + EDB 1.00 build 6)

Peter,

<< Is RESTRICT the only available option for referential integrity
constraints? >>

Yes, RESTRICT is the only option currently.  ElevateDB doesn't have the
proper locking mechanisms in place currently to allow for cascades without
causing concurrency issues.

--
Tim Young
Elevate Software
www.elevatesoft.com

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote:

<< Yes, RESTRICT is the only option currently.  ElevateDB doesn't have the
proper locking mechanisms in place currently to allow for cascades without
causing concurrency issues. >>

I guess I would need to base RI on triggers or something similar (trap the error and do it programmatically?). This is where my SQL knowledge starts to
expire pretty rapidy so perhaps it is a good opportunity to expand it.

Peter

Peter,

<< I guess I would need to base RI on triggers or something similar (trap
the error and do it programmatically?). This is where my SQL knowledge
starts to expire pretty rapidy so perhaps it is a good opportunity to expand
it. >>

Unfortunately you can't trap the error and handle it via a trigger.  The
only way to handle the situation is to wrap the entire update in a
transaction, and make sure that you have an AfterUpdate trigger defined that
cascades any changes down the appropriate tables.  It's a little kludgy,
unfortunately.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

Do you mean something like this:

CREATE TRIGGER "after_delete" AFTER DELETE ON "Models"
BEGIN
 DECLARE StakeholderCursor SENSITIVE CURSOR FOR StakeholderStatement;
 PREPARE StakeholderStatement FROM 'SELECT mid_local FROM Stakeholders WHERE Stakeholders.mid_local = deleted.mid_local';
 OPEN StakeholderCursor;
 START TRANSACTION ON TABLES Stakeholders;
 BEGIN
   WHILE NOT EOF(StakeholderCursor) DO DELETE FROM StakeholderCursor;
   END WHILE;
 COMMIT;
 EXCEPTION
   ROLLBACK;
 END;
END

It doesn't seem that kludgy ... but it doesn't work. Grateful for any pointers as to where I am going wrong. On executing the SELECT query in the PREPARE statement throws an error that column delete.mid_local does not exist (same error if
I use models.mid_local). I also tried the very simple

CREATE TRIGGER "after_delete" AFTER DELETE ON "Models"
BEGIN
 DELETE FROM Stakeholders WHERE Stakeholders.mid_local IN (SELECT mid_local FROM deleted)
END

but this won't execute (Error #700 Expected ; but instead found WHERE)

Thanks

Peter

Peter,

<< Do you mean something like this: >>

Yes.

<< It doesn't seem that kludgy ... but it doesn't work. Grateful for any
pointers as to where I am going wrong. On executing the SELECT query in the
PREPARE statement throws an error that column delete.mid_local does not
exist (same error if I use models.mid_local). >>

Very close. You need to use OLDROW.mid_local to reference the row that
was just deleted.  In triggers, the "system" rows are OLDROW and NEWROW.

--
Tim Young
Elevate Software
www.elevatesoft.com