how to script a delete on dependent tables...

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

how to script a delete on dependent tables...

Author

Topic

stuckne1
Starting Member

22 Posts

Posted - 2010-11-01 : 14:50:58

For example the two tables here...I'm want to have the related row deleted in the 2nd table if a row gets deleted in the first row.

USE [MyDB_Workstudy]
GO
/****** Object: Table [dbo].[Users] Script Date: 11/01/2010 13:46:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Users](
[parkId] [int] NOT NULL,
[firstName] [text] NULL,
[lastName] [text] NULL,
[email] [text] NULL,
[role_id] [int] NOT NULL,
[confidentialityAgreement] [text] NULL,
[id] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[parkId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

USE [MyDB_Workstudy]
GO
/****** Object: Table [dbo].[UsersParkIdOnly] Script Date: 11/01/2010 13:47:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[UsersParkIdOnly](
[id] [int] IDENTITY(1,1) NOT NULL,
[parkId] [int] NOT NULL
) ON [PRIMARY]

stuckne1
Starting Member

22 Posts

Posted - 2010-11-01 : 14:51:46

For example the two tables here...I'm want to have the related row deleted in the 2nd table if a row gets deleted in the first table.*

Kristen
Test

22859 Posts

Posted - 2010-11-01 : 15:00:46

Either:

1) set up a Foreign Key with Cascade Delete
2) Set up a Trigger on the Parent record that deletes any Child records (but then you cannot have a foreign key)
3) Use a Stored procedure to delete records from Parent Table and have the SProc pre-delete any Child records (in a transaction block so that either the parent AND any children are deleted, or [if error / power-cut etc.] then neither are deleted.

stuckne1
Starting Member

22 Posts

Posted - 2010-11-01 : 15:33:55

Okay, I've been trying a variety of ways to use Cascading delete and I came up with this...but I have a few syntax errors...

USE [MyDB_Workstudy]
GO
/****** Object: Table [dbo].[Users] Script Date: 11/01/2010 14:29:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TABLE [dbo].[Users](
[parkId] [int] NOT NULL,
[firstName] [text] NULL,
[lastName] [text] NULL,
[email] [text] NULL,
[role_id] [int] NOT NULL,
[confidentialityAgreement] [text] NULL,
[id] [int] IDENTITY(1,1) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

ALTER table [dbo].[Users] ADD CONSTRAINT
(FOREIGN KEY (id) REFERENCES UsersParkIdOnly
ON DELETE CASCADE CONSTRAINT id)

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '('.
Msg 102, Level 15, State 1, Line 12
Incorrect syntax near '('.

stuckne1
Starting Member

22 Posts

Posted - 2010-11-01 : 15:37:52

this is sql server 2005 is that makes a difference

stuckne1
Starting Member

22 Posts

Posted - 2010-11-01 : 15:59:27

never mind. Thanks!

Kristen
Test

22859 Posts

Posted - 2010-11-01 : 16:16:59

I should have said that I'm not that keen on Cascade Delete FKeys ...

If my application says

DELETE ParentTable WHERE ID=xxx

then a cascading foreign key will delete any children. But maybe I thought there were NO children but there was a bug in my program

Whereas if I just had an FKey (NO cascade delete) and I did the same Delete then I would get error "Child rows exist".

So I prefer, in my application, to decide that a particular delete operation should delete any children if they exist (maybe the user had to tick an extra checkbox, so there is an extra parameter to my SProc such as @ParamUserConfirmedChildDeletes that I use to conditionally pre-delete child records.

Either way, if something goes wrong in my application it is likely that it will fail on the side of a "Delete failed, child records exist" error message - rather tahn silently deleting data that I hadn't realised was there

Your call though, of course

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources