| Author |
 Topic  |
|
|
stuckne1
Starting Member
USA
22 Posts |
 Posted - 11/01/2010 : 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
USA
22 Posts |
Posted - 11/01/2010 : 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
United Kingdom
22191 Posts |
Posted - 11/01/2010 : 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
USA
22 Posts |
Posted - 11/01/2010 : 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
USA
22 Posts |
Posted - 11/01/2010 : 15:37:52
|
| this is sql server 2005 is that makes a difference |
|
|
|
stuckne1
Starting Member
USA
22 Posts |
Posted - 11/01/2010 : 15:59:27
|
| never mind. Thanks! |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 11/01/2010 : 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  |
|
|
| |
Topic |
|
how to script a delete on dependent tables...
