CASCADE Question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

CASCADE Question

Author

Topic

X002548
Not Just a Number

15586 Posts

Posted - 2006-11-06 : 11:43:04

OK, so now I'm just being lazy, but after seeing a question about this, I wonder which is actually better?

Assuming that you have SPROC only access to your data, which is better/more effeicient.

Having the stored procedure actually do the deletes/updates (which is the way I always do this) or enabling CASCADE DELETE/UPADTE in the database?

Just curious, what do most people do.

Logically to me, the "UPDATE" is actually a logical DELETE and INSERT anyway because it's a new "thing"

Unless of course you buy into surrogates

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Kristen
Test

22859 Posts

Posted - 2006-11-06 : 11:53:41

I'd do the delete in the SProc and have the FKey as a "Long Stop".

Some twit might DROP the KFey one day ...

Kristen

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-11-06 : 12:21:43

we use cascades.
of course we don't use stored procedures at all.
of yourse i'm not happy about it.
of course our code looks like crap and is very injection friendly.
and of course i couldn't care less anymore... i used to care

i dislike cascades because it adds another layer (count triggers here too) of things that you'd
eventually forget about if nothing goes wrong in a while.

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-11-06 : 16:52:23

This sounds like a "It depends" question.

Cascades (and triggers) can become hidden mines in the database, no doubt about it.

However there are cases when a cascade is the behaviour that one wants;
yes we really want those child records removed / updated if something happens to the parent.

If integrity (and no accidental deletes) is the guiding light, then don't use them,
yes we really want a FK violation error if some schmuck deletes this record.

I have used cascades occasionally when they seem to fit my purpose.
The concept of having the database do the dirty clean-up work itself has an appealing ring to me, sometimes.

What is more efficient? I have no idea actually, and have made no tests either way.
But it is an interesting question, are there optimizations under the hood for cascades, or is there no difference?

Cascade Update is interesting when dealing with natural keys.
dealing with an update (sorry insert/delete) can be a bit of a drag if there are many child tables,
the cascade update makes this a no - problem (except for the strain on the db of course).
And are not near as dangerous as a cascade delete!
In practice though, I have yet to see a natural key that is updated without serious thought, and the willingness to do the extra work.

I think it is a nice tool to have these cascading fk's, even if used rarely.

Best practice...
that's pretty straightforward in my mind, delete or update something with child records = FK violation!
Let's not make it easy to make any stupid mistakes!

rockmoose

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-11-06 : 17:30:05

>>Just curious, what do most people do.
We don't typically implement any cascades. We make the user (via "friendly" messages) delete any dependant data themselves. Plus everything is logged so when someone says, "hey, where'd my crap go?!" we can say...

>>our code...is very injection friendly.

Be One with the Optimizer
TG

robvolk
Most Valuable Yak

15732 Posts

Posted - 2006-11-06 : 17:47:35

quote:
Logically to me, the "UPDATE" is actually a logical DELETE and INSERT anyway because it's a new "thing"

This is not true. SQL Server has had update-in-place since 7.0, and I think even in 6.5. It is not a "new thing" unless you change the primary key of the row. And regardless of the physical implementation, I don't think it's logical to consider UPDATE as DELETE/INSERT; otherwise you wouldn't have an UPDATE statement in the SQL standard.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-11-06 : 21:58:12

for archiving purposes on a reporting server, we've implemented a cascade delete,coz we don't need to worry about constraint violations, tables being locked down during a massive delete (child gets deleted then parent... problem is if you have n-number of children and leaves, the batch becomes too big)

oh, you were asking about cascade update, we don't, information are not duplicated in tables in the same database, so one update will only affect a specific table... we don't update PKs

--------------------
keeping it simple...

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2006-11-06 : 23:24:19

I'm a cascade fan...

Declaring behaviour is so much easier than writing the behaviour.

DavidM

Production is just another testing cycle

Kristen
Test

22859 Posts

Posted - 2006-11-07 : 01:13:05

"What is more efficient? I have no idea actually, and have made no tests either way."

My perception is that the FK stuff is slow.

Sometimes I delete a record manually that fails FK, and my initial reaction is "What's going on" ... and then I see the FK violation error.

Maybe the delete is fast when there is no FK violation?!

And that doesn't have anything to do with cascade delete of course ...

... cascades beings a Worktable to the party, I'm afraid:


CREATE TABLE dbo.K_Parent
(
	P_ID int NOT NULL,
	PRIMARY KEY
	(
		P_ID
	)
)

CREATE TABLE dbo.K_Child
(
	C_ID int NOT NULL,
	C_P_ID int NOT NULL,
	PRIMARY KEY
	(
		C_ID
	)
)
GO

-- Foreign key - without cascade delete
ALTER TABLE dbo.K_Child ADD CONSTRAINT
	FK_K_Child_K_Parent FOREIGN KEY
	(
		C_P_ID
	) REFERENCES dbo.K_Parent
	(
		P_ID
	)
GO

INSERT INTO dbo.K_Parent(P_ID)
SELECT 100 UNION ALL
SELECT 200 UNION ALL
SELECT 300 UNION ALL
SELECT 400 UNION ALL
SELECT 500 UNION ALL
SELECT 600 UNION ALL
SELECT 700 UNION ALL
SELECT 800 UNION ALL
SELECT 900

INSERT INTO dbo.K_Child(C_ID, C_P_ID)
SELECT 101, 100 UNION ALL -- Several children for Parent=100
SELECT 102, 100 UNION ALL
SELECT 103, 100 UNION ALL
SELECT 104, 100 UNION ALL
SELECT 105, 100 UNION ALL
SELECT 106, 100 UNION ALL
SELECT 107, 100 UNION ALL
SELECT 108, 100 UNION ALL
SELECT 109, 100 UNION ALL
SELECT 201, 200 UNION ALL
SELECT 301, 300 UNION ALL
SELECT 401, 400 UNION ALL
SELECT 501, 500 UNION ALL
SELECT 601, 600 UNION ALL
SELECT 701, 700 UNION ALL
SELECT 801, 800 UNION ALL
SELECT 901, 900
GO

CREATE INDEX IX_K_Child_C_P_ID ON dbo.K_Child
(
	C_P_ID
)
GO


-- SET SHOWPLAN_TEXT ON
GO
SET STATISTICS IO ON; SET STATISTICS TIME ON
GO

DECLARE	@My_P_ID int
SELECT	@My_P_ID = 100

DELETE	dbo.K_Child WHERE C_P_ID = @My_P_ID
-- Without index:
-- Table 'K_Child'. Scan count 1, logical reads 20, physical reads 0, read-ahead reads 0.
-- With index:
-- Table 'K_Child'. Scan count 1, logical reads 37, physical reads 0, read-ahead reads 0.

DELETE	dbo.K_Parent WHERE P_ID = @My_P_ID
-- WITHOUT FK:
-- Table 'K_Parent'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.
-- WITH FK without index:
-- Table 'K_Child'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.
-- Table 'K_Parent'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.
-- WITH FK with index:
-- Table 'K_Child'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0.
-- Table 'K_Parent'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.

GO

SET STATISTICS IO OFF; SET STATISTICS TIME OFF
GO
SET SET SHOWPLAN_TEXT OFF
GO

ALTER TABLE dbo.K_Child DROP CONSTRAINT FK_K_Child_K_Parent
GO
TRUNCATE TABLE dbo.K_Parent
TRUNCATE TABLE dbo.K_Child
GO

-- Foreign key - with cascade delete
ALTER TABLE dbo.K_Child ADD CONSTRAINT
	FK_K_Child_K_Parent FOREIGN KEY
	(
		C_P_ID
	) REFERENCES dbo.K_Parent
	(
		P_ID
	)
	ON DELETE CASCADE 
GO

-- Reinstate same data
INSERT INTO dbo.K_Parent(P_ID)
SELECT 100 UNION ALL
SELECT 200 UNION ALL
SELECT 300 UNION ALL
SELECT 400 UNION ALL
SELECT 500 UNION ALL
SELECT 600 UNION ALL
SELECT 700 UNION ALL
SELECT 800 UNION ALL
SELECT 900

INSERT INTO dbo.K_Child(C_ID, C_P_ID)
SELECT 101, 100 UNION ALL
SELECT 102, 100 UNION ALL
SELECT 103, 100 UNION ALL
SELECT 104, 100 UNION ALL
SELECT 105, 100 UNION ALL
SELECT 106, 100 UNION ALL
SELECT 107, 100 UNION ALL
SELECT 108, 100 UNION ALL
SELECT 109, 100 UNION ALL
SELECT 201, 200 UNION ALL
SELECT 301, 300 UNION ALL
SELECT 401, 400 UNION ALL
SELECT 501, 500 UNION ALL
SELECT 601, 600 UNION ALL
SELECT 701, 700 UNION ALL
SELECT 801, 800 UNION ALL
SELECT 901, 900
GO

-- SET SHOWPLAN_TEXT ON
GO
SET STATISTICS IO ON; SET STATISTICS TIME ON
GO

DECLARE	@My_P_ID int
SELECT	@My_P_ID = 100

DELETE	dbo.K_Parent WHERE P_ID = @My_P_ID
-- Without index:
-- Table 'K_Child'. Scan count 1, logical reads 20, physical reads 0, read-ahead reads 0.
-- Table 'Worktable'. Scan count 2, logical reads 4, physical reads 0, read-ahead reads 0.
-- Table 'K_Parent'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.
-- With index
-- Table 'K_Child'. Scan count 10, logical reads 28, physical reads 0, read-ahead reads 0.
-- Table 'Worktable'. Scan count 1, logical reads 11, physical reads 0, read-ahead reads 0.
-- Table 'Worktable'. Scan count 2, logical reads 4, physical reads 0, read-ahead reads 0.
-- Table 'K_Parent'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.


GO

SET STATISTICS IO OFF; SET STATISTICS TIME OFF
GO
SET SET SHOWPLAN_TEXT OFF
GO

ALTER TABLE dbo.K_Child DROP CONSTRAINT FK_K_Child_K_Parent
GO
DROP TABLE dbo.K_Parent
GO
DROP TABLE dbo.K_Child
GO

Kristen

X002548
Not Just a Number

15586 Posts

Posted - 2006-11-07 : 09:01:30

quote:
Originally posted by robvolk

quote:
Logically to me, the "UPDATE" is actually a logical DELETE and INSERT anyway because it's a new "thing"
This is not true. SQL Server has had update-in-place since 7.0, and I think even in 6.5. It is not a "new thing" unless you change the primary key of the row. And regardless of the physical implementation, I don't think it's logical to consider UPDATE as DELETE/INSERT; otherwise you wouldn't have an UPDATE statement in the SQL standard.

Rob, I was refering to an update of the PK

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

hizuka
Starting Member

7 Posts

Posted - 2006-11-10 : 09:00:30

I use cascade whenever it fits into my purpose. I really don't have the reason to write code to delete records in the child table when the cascade feature is so easy to use.

Hizuka - http://www.sqlref.com

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-10 : 09:12:38

Well, I am not a fan of automatic things like cascade...they make me feel bit insecure.

Instead I prefer writing SPs which gives me more control. Also, writing SP/trigger is also preferable when you want to do something more than just update/delete (like keeping log).

Harsh Athalye
India.
"Nothing is Impossible"

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-11-12 : 16:56:02

> "My perception is that the FK stuff is slow."
I agree, and sometimes altering the constraints to NOCHECK is an idea when doing major data movements.

rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources