Cascading deletes - Which is better - A Trigger or Foreign Key cascading delete?

I need to implement my cascading deletes on a SQL database.  Is it better (performance/reliablility-wise) to use the Foreign Key Cascading Deletes or to just write my own triggers to do the deletes?

I was hoping someone had experimented and found which works best.
0
KenP2600
8/17/2005 4:44:33 PM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

2 Replies
785 Views

Similar Articles

[PageSpeed] 12

DRI(declarative referential integrity) triggers are used when Cascade Deletes and Updates cannot be implemented in the RDBMS(relational database management system) as in SQL Server 7.0 and below.  In SQL Server 2000 using Cascade Delete and Cascade Update is the way to go.  The reason is reliability related because DRI(declarative referential integrity) with Cascade Delete and Cascade Update is guaranteed. 

On a side note if you have a why question run a search for Peter Chen 1976 ERD paper on Google and see the 26 pages paper in Algebra that created DRI(declarative referential integrity) in ANSI SQL.  Hope this helps.
Kind regards,
Gift Peddie
0
Caddre
8/17/2005 10:00:10 PM
Thanks for the great info.  All my servers run SQL 2000 so we'll be going with the built-in cascading deletes.
0
KenP2600
9/6/2005 5:31:23 PM
Reply:

Similar Artilces:

Copy and Delete Table with "Foreign Key References(...,...) On Delete Cascade?
Hello: Need some serious help with this one... Background: Am working on completing an ORM that can not only handles CRUD actions -- but that can also updates the structure of a table transparently when the class defs change. Reason for this is that I can't get the SQL scripts that would work for updating a software on SqlServer to be portable to other DBMS systems. Doing it by code, rather than SQL batch has a chance of making cross-platform, updateable, software... Anyway, because it needs to be cross-DBMS capable, the constraints are that the system used must work for the low...

Foreign Key delete cascade
Hi everybody, I 'm new in the group and this question may have posted in the past but, I'm using ASE 12.5, and I 'm using ALTER TABLE statements to enforce referencial integrity like : ALTER TABLE table1 ADD CONSTRAINT fc1_table1_table2 FOREIGN KEY (id1,id2) REFERENCES table2(id1,id2) Since ASE does not provide FK cascade delete, is there anything I can do on "Database Level" instead of putting all code into Application ? thanks in advance, Jim Write a trigger to do it on other tables <Jim> wrote in message news:F9C486E5ED5CA2150046799...

cascade delete of foreign keys?
I came from ASA and in it I can define foreign keys with 'on delete' keyword. For example create table groups ( group_id integer not nup primary key, ... other columns); create table objects ( object_id integer not nup primary key, group_id integer not null references groups on delete cascade, ... other columns); This will forbid user to create object with bad group_id, and automaticaly delete all objects when group is deleted. How can I do this in ASE 12.5? In ASE you have to use triggers to implement this functionality. HTH, Rob V. -----------...

cascade delete can not apply to two foreign keys
I have a table that contains two foreign keys of two different tables. I want to build a relationship so that when either primary keys deleted in the two tables, the record in the table should be deleted. But, SQL Server does not allow me to save the relationship, it complains that the circling delete might exist. I do not know why, how can I solve this? Table A: ID ProductID <foreign key> CustomerID <foreign key> Table Product ProductID <primary key> Table Customer CustomerID <primary key> I want to cascade delete the record in Table A when...

Foreign Key Ultralite Database CASCADE delete and Update
This is a multi-part message in MIME format. ---=_forums-1-dub40929294 Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: 7bit hello, I got two ultralite tables (Athletes and Penalties), In table ATHLETES i got the primary key (ID) and in the table PENALTIES i got the foreign key (ID) related to the primary key (ID) on table ATHLETES,.., in the Ultralite Schema Painter i can defined the foreign key but i cannot defined the cascade delete and update for this foreign key, how can i do it? In ASA you can defined the cascade delete and update for a...

Upgraded to version 11 and now Foreign Keys are not Cascading the deletes
Our customer upgraded to version 11 of SQL Anywhere from 9. There are a number of tables that compose a master detail relationship. The database was designed to use the foreign key relationship between the 2 tables to cascade the deletes when the master (primary_key) was deleted, in that the action for Delete was set to Cascade. Now with version 11 an SQL error (23000) is generated for the reference of the foreign key in the detail table when deletes occur. I read the help file and did not see any "changes in behavior" which would effect this. I did notice thaty w...

Delete on Cascade in Trigger
I want to do a deletion on cascade in trigger. When I execute this command : DELETE FROM ENTIDADE WHERE ENTCOD = '02346' I receive this message: Server Message: Number 547, Severity 16 Line 1: Dependent foreign key constraint violation in a referential integrity constraint. dbname = 'apolo', table name = 'entidade', constraint name = 'FK_ENT_CATE_REF_2630_ENTIDADE'. Can I delete a child table in trigger ? This is my code: ---------------------------------------------------------------------------- --------------------...

Cascading Deletes With Other Triggers
Cascading deletes have stopped working since I added a trigger to (almost) every table in my database. The new trigger is called before the delete and writes an entry to a 'deletions log table'. But there is no foreign key relationship between the 'deletions log table' and the other tables. After some investigation, I have identified that a cascading delete works where my extra trigger exists only on the detail table. If the 'deletions log' trigger is added to the master table, the cascading delete fails with the message "Primary key for row in tab...

delete cascade trigger problem
...

Cascade deletes in SQL 2005
I have a logins table, a loginroles table (intermediate), and a roles table When I delete a login from logins I need to delete the roles for that login from loginroles. I know I have to use cascade deletes, but I cannot find the option in sql 2005. Any help would be appreciated. Thank You,Jason It is there even in Express edition, you should see enable DRI(declarative referential integrity) at the top of Management Studio.  BTW SQL Server 2005 also comes with the other two ANSI SQL definition DRI SET NULL and SET DEFAULT.  Try the links below for more from Microsoft ...

Is Cascading deletes prefered to deleting thru a stored procedure?
I have a situation in which I have three tables - 'Table1', 'Table2' and 'Table3'. 'Table1' is a primary table for 'Table2' so that the the primary key of 'Table1' is a foreign key in 'Table2'. In the same way, 'Table1' is also the primary table for 'Table3'. If I delete a row in 'Table1' then I could automatically let SQL Server also delete the related rows in 'Table2' and 'Table3' through 'cascading delete' option. Or I could run a trigger or stored procedure for deleting related rows in 'Table2' and 'Table3'. Which of these methods would be quicker? Also would cascading deletes option a...

delete cascade
I'm trying to add delete cascade to an already-existing table via ISQL. I'm getting a syntax error: alter table carrier on delete cascade; Error = syntax error near 'on' Should I be able to do this? I'm running on Windows 2000 Pro, SQLAnywhere 5.5.03, using DBENG50.EXE. It should be something like: alter table carrier drop foreign key fk1 alter table carrier add foreign key fk1 references ... etc ... on delete cascade Then when the row in the fk1 table is deleted the row in the "carrier" table will be deleted also. -- Steve http://www...

CASCADE deletes
In SQL Central, how do I set sqlanywhere to enforce referential integrety and delete all foreign keys when a record is deleted? Thanks for any help, Randy Nelson >In SQL Central, how do I set sqlanywhere to enforce referential integrety >and delete all foreign keys when a record is deleted? To create a foreign key you can click on the "Add foreign Key" line to start a fkey creation wizard. To change the properties of an existing foreign key definition, Right click on the foreign key and choose Properties. Click the 'Integrity' tab. Under "de...

Cascading Deletes
In PowerDesigner 9.5.2, is it possible to have all relationships in the PDM which have cascading deletes highlighted in a different color than the others. I'm hoping there is a way other than manually changing the color on each one. If you can handle a little VB Script writing, you can do it. There are a few sample scripts to start with. What you will need to do is: Spin through all of the references. For each, if the type is on delete cascade, then set the color attribute to something special. It really shouldn't be a lot of coding. If you need help, post back here ...

Web resources about - Cascading deletes - Which is better - A Trigger or Foreign Key cascading delete? - asp.net.sql-datasource

Cascading Style Sheets - Wikipedia, the free encyclopedia
Cascading Style Sheets ( CSS ) is a style sheet language used for describing the presentation semantics (the look and formatting) of a document ...

Ads API Profile: Alchemy’s Cascading Ad Creation, Analytics, and Auto-Optimization
Facebook’s performance advertising system provides buyers with a wealth of targeting options, but not the depth of control or analytics to effectively ...

Twitter blames global outage on 'cascading bug'
AFTER nearly a year without any significant periods of downtime, using Twitter was almost nothing like the early days of being on the service. ...

In defence of Gen Y, and cascading generations, by a boomer
Some accuse Generation Y of feeling entitled, overly precocious, demanding and rude but that doesn't necessarily reflect reality.

In defence of Gen Y, and cascading generations, by a boomer
Some accuse Generation Y of feeling entitled, overly precocious, demanding and rude but that doesn't necessarily reflect reality.

Cascading Style Sheets - 9to5Mac
Adobe announced updates on Thursday to many of its HTML5 design tools for Creative Cloud users. Among the updates, we get new features for Edge ...

West Texas Cascading Safety Failure
In the interests of Calamity Fairness, here’s a most depressing Bloomberg article : Texas Explosion Seen as Sign of Weak U.S. Oversight The ...

Watch this captivating video of 11,000 marbles cascading down a marble run
Everyone has a hobby. Some people like to play recreational sports, others prefer to go fishing, but did you know there are creative types out ...

Austria Announces Fence With Slovenia; Irony of German Whine; Cascading Fences; Mish Proposed Strategy ...
Two months ago, Austria's chancellor Werner Faymann criticized Hungary for building a fence along its border. Faymann proclaimed "To think that ...

Cascading Slopes is Jacob from The Drums' new band, NYC residency this month
Cascading Slopes at Cameo, 1/31/2013 (via @FreshBread) Cascading Slopes is a new project from Jacob Graham of The Drums. Unlike his other band, ...

Resources last updated: 12/30/2015 11:00:32 AM