Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 7 of 7 total |
No CASCADE? |
Thu, Apr 26 2007 5:57 AM | Permanent Link |
Peter Thorne | 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 |
Fri, Apr 27 2007 6:39 AM | Permanent Link |
Chris Erdal | 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) |
Fri, Apr 27 2007 8:03 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Fri, Apr 27 2007 2:10 PM | Permanent Link |
Peter Thorne | "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 |
Mon, Apr 30 2007 3:38 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Tue, May 1 2007 6:04 AM | Permanent Link |
Peter Thorne | 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 |
Wed, May 2 2007 11:30 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
This web page was last updated on Tuesday, May 7, 2024 at 02:40 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |