SQL Server deletion dilemma

Question

CREATE TABLE [dbo].[ProjectTasks]
(
    [TaskID] [int] IDENTITY(1,1) NOT NULL,
    ...
    [DefaultTaskValue] [int] NULL
)

ALTER TABLE [dbo].[ProjectTasks] ADD  CONSTRAINT [PK_ProjectTasks] PRIMARY KEY 
CLUSTERED 
(
    [TaskID] ASC
)

ALTER TABLE [dbo].[ProjectTasks] ADD CONSTRAINT [FK_ProjectTasks_TaskValues] 
FOREIGN KEY([TaskID], [DefaultTaskValue])
REFERENCES [dbo].[TaskValues] ([TaskID], [Value])
GO

CREATE TABLE [dbo].[TaskValues]
(
    [TaskID] [int] NOT NULL,
    [Value] [int] NOT NULL,
    ...
)

ALTER TABLE [dbo].[TaskValues] ADD  CONSTRAINT [PK_TaskValues] PRIMARY KEY CLUSTERED 
(
    [TaskID] ASC,
    [Value] ASC
)

ALTER TABLE [dbo].[TaskValues] ADD CONSTRAINT [FK_TaskValues_ProjectTasks] 
FOREIGN KEY([TaskID])
REFERENCES [dbo].[ProjectTasks] ([TaskID])
GO

Given the above, no record in either table can be deleted - is there any neat solution? What I'd LIKE to do is sort the design to make the link table a pure link table and not to itself carry the value. This has not been a problem up to now because rows were never required to be actually deleted (they were flagged, but not actually deleted), and of course you can insert because DefaultValue can be NULL.

EDIT: In response to my downmarker, I should have mentioned, I have considered both ON DELETE CASCADE and a TRIGGER, I am looking for alternatives to those scenarios, and clearly ON DELETE SET NULL will not work.

One possibility is to have `ON DELETE CASCADE` added to your foreign key constraint, but that could potentially trigger a huge chain of record deletions from both tables. — Aleks G, Feb 14 '12 at 11:00
Considered that - not really viable. Also considered an after insert/update trigger on ProjectTasks (inserts have to always go in with NULL for DefaultValue, updates would be checked). — kpollock, Feb 14 '12 at 11:04
You should remove the `DefaultTaskValue` column and create another `DefaultTaskvalue` table. This way, all columns can be set as `NOT NULL`. — ypercubeᵀᴹ, Feb 14 '12 at 11:15
@ypercube - yes, that's what I meant about a pure link table. For another iteration! — kpollock, Feb 15 '12 at 12:05

score 1 · Accepted Answer · answered Feb 14 '12 at 11:20

I was surprised by your statement

and of course you can insert because DefaultValue can be NULL

and had to look it up. MSKB says

Specifically, if a composite foreign key has been defined on columns that allow nulls, and at least one of the columns, upon the insert or update of a row, is set to null, then the foreign key constraint will be satisfied. This is true even if there is not a row in the related table to which any of the corresponding columns are matched.

I guess I was not aware of this information. So I suggest the following commands:

UPDATE ProjectTasks SET DefaultTaskValue = NULL *W
DELETE TaskValues *W
DELETE ProjectTasks *W

*W is your WHERE condition (on TaskID etc)

ah - yeah brilliant, that's what I should have realised I could do. Off to try it... — kpollock, Feb 14 '12 at 11:36

score 0 · Answer 2 · answered Feb 14 '12 at 11:23

One solution is to remove the DefaultTaskValue column and create another DefaultTaskvalues table. This way, all columns can be set as NOT NULL and cascading effects as you please.

ProjectTasks table:

CREATE TABLE [dbo].[ProjectTasks]
(
    [TaskID] [int] IDENTITY(1,1) NOT NULL,
    ...
                         --- removed: DefaultTaskValue] [int] NULL
)

ALTER TABLE [dbo].[ProjectTasks] 
  ADD CONSTRAINT [PK_ProjectTasks] 
  PRIMARY KEY CLUSTERED 
(
    [TaskID] ASC
)
GO

TaskValues table:

CREATE TABLE [dbo].[TaskValues]
(
    [TaskID] [int] NOT NULL,
    [Value] [int] NOT NULL,
    ...
)

ALTER TABLE [dbo].[TaskValues] 
  ADD CONSTRAINT [PK_TaskValues] 
  PRIMARY KEY CLUSTERED 
(
    [TaskID] ASC,
    [Value] ASC
)

ALTER TABLE [dbo].[TaskValues]
  ADD CONSTRAINT [FK_TaskValues_ProjectTasks] 
  FOREIGN KEY([TaskID])
  REFERENCES [dbo].[ProjectTasks] ([TaskID])
GO

DefaultTaskValues table:

CREATE TABLE [dbo].[DefaultTaskValues]
(
    [TaskID] [int] NOT NULL,
    [Value] [int] NOT NULL,
    ...
)

ALTER TABLE [dbo].[DefaultTaskValues] 
  ADD CONSTRAINT [PK_DefaultTaskValues] 
  PRIMARY KEY CLUSTERED 
(
    [TaskID] ASC
)

ALTER TABLE [dbo].[DefaultTaskValues] 
  ADD CONSTRAINT [FK_DefaultTaskValues_TaskValues] 
  FOREIGN KEY([TaskID], [Value])
  REFERENCES [dbo].[TaskValues] ([TaskID], [Value])
GO

SQL Server deletion dilemma

2 Answers2