Cascade Deletes - Dev Shed

Dev Shed Forums Sponsor:

May 29th, 2006, 09:55 AM

stevesoft

Registered User

Join Date: May 2006

Location: ENGLAND

Posts: 3

Time spent in forums: 47 m 9 sec
Reputation Power: 0

Cascade Deletes

Can anyone help me with setting up cascade deletes in a firebird database.

I am using Upscene's Database Workbench 2.8 for the database administration, and VB6 for the front-end application.

I've tried to setup a foreign key constraint and selected cascade delete, however it does not seem to work.

This is what i need to achieve;

I have two tables;

1) Ranges (with three fields)
RA_ID <-----Primary Key
RA_NAME
RA_DESC

2) Models (with four fields)
MO_ID <-----Primary Key
MO_NAME
MO_DESC
MO_RA_ID <----Foreign Key

the models belong to a range, and the field MO_RA_ID indicates which range it belongs to and needs to be linked to RA_ID in the ranges table.

If a range is deleted, all the models that belong to that range should also be deleted.

Last edited by stevesoft : May 29th, 2006 at 10:11 AM. Reason: more info

May 29th, 2006, 10:30 AM

pabloj

Modding: Oracle MsSQL Firebird

Dev Shed God 6th Plane (7500 - 7999 posts)

Join Date: Jun 2001

Location: Outside US

Posts: 7,906

Time spent in forums: 2 Months 3 Weeks 3 Days 6 h 56 m 54 sec
Reputation Power: 279

This is a simple example:

sql Code:

Original - sql Code
CREATE TABLE TEST_HEADER( FIRST_FIELD Varchar(10) NOT NULL COLLATE NONE, PRIMARY KEY (FIRST_FIELD) );

CREATE TABLE TEST_HEADER(
  FIRST_FIELD Varchar(10) NOT NULL COLLATE NONE,
  PRIMARY KEY (FIRST_FIELD)
);

sql Code:

Original - sql Code
CREATE TABLE TEST_DETAILS( FIRST_FIELD Varchar(10) NOT NULL COLLATE NONE, SECOND_FIELD Varchar(10) NOT NULL COLLATE NONE, PRIMARY KEY (FIRST_FIELD,SECOND_FIELD) );

CREATE TABLE TEST_DETAILS(
  FIRST_FIELD Varchar(10) NOT NULL COLLATE NONE,
  SECOND_FIELD Varchar(10) NOT NULL COLLATE NONE,
  PRIMARY KEY (FIRST_FIELD,SECOND_FIELD)
);

Then add the FK

sql Code:

Original - sql Code
ALTER TABLE TEST_DETAILS ADD CONSTRAINT FK_TEST_DETAILS_1 FOREIGN KEY (FIRST_FIELD) REFERENCES TEST_HEADER (FIRST_FIELD) ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE TEST_DETAILS 
ADD CONSTRAINT FK_TEST_DETAILS_1  
FOREIGN KEY (FIRST_FIELD) REFERENCES TEST_HEADER (FIRST_FIELD) 
ON UPDATE CASCADE ON DELETE CASCADE;

it works (check your commits

)

__________________
My blog
Tutorials about OSS databases, DBMonster ...
Contribute to OSS Development, fill bug reports!
Developer Shed eSupport
Commented my.ini/my.cnf (ADD YOUR OWN CONFIG TRICK)
An introduction to database normalization
Natural or Surrogate key
Custom ordering for your results
Correlated and uncorrelated subqueries
Don't turn your outer joins into inner joins
Random data (with a bias)

May 29th, 2006, 12:26 PM

stevesoft

Registered User

Join Date: May 2006

Location: ENGLAND

Posts: 3

Time spent in forums: 47 m 9 sec
Reputation Power: 0

This is the SQL i try to execute;

sql Code:

Original - sql Code
ALTER TABLE MODELS ADD CONSTAINT FK_MODELS_1 FOREIGN KEY (MO_RA_ID) REFRENCES RANGES (RA_ID) ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE MODELS
ADD CONSTAINT FK_MODELS_1
FOREIGN KEY (MO_RA_ID) REFRENCES RANGES (RA_ID)
ON UPDATE CASCADE
ON DELETE CASCADE;

but then i get the following error;

Quote:

ISC ERROR CODE:335544466

ISC ERROR MESSAGE:
VIOLATION OF FOREIGN KEY CONSTRAINT "PK_RANGES" ON TABLE "RANGES"

May 29th, 2006, 01:21 PM

stevesoft

Registered User

Join Date: May 2006

Location: ENGLAND

Posts: 3

Time spent in forums: 47 m 9 sec
Reputation Power: 0

I Got It Working

I Deleted All The Data From Both Tables,
Added The Foreign Key, Which This Time Did Not Give The Error.
Then Re-generated All The Data

Now It Seems To Work Ok. I Think I Must Of Had Some Corrupt Data In There

May 30th, 2006, 02:32 AM

pabloj

Modding: Oracle MsSQL Firebird

Join Date: Jun 2001

Location: Outside US

Posts: 7,906

Time spent in forums: 2 Months 3 Weeks 3 Days 6 h 56 m 54 sec
Reputation Power: 279

Quote:

Originally Posted by stevesoft

I Got It Working
...
Now It Seems To Work Ok. I Think I Must Of Had Some Corrupt Data In There

Yes, that's what FK are for

Glad you got it working

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: