OK, Here's the DDL for the 2 tables in question (minus some irrelevant constraints).
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[KeyWords]') AND type in (N'U'))
DROP TABLE [dbo].[KeyWords]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[KeyWords](
[nKeyWord_Id] [int] IDENTITY(1,1) NOT NULL,
[cKeyword] [varchar](255) NOT NULL,
[nCLT_Id] [int] NOT NULL,
[vKeyword] AS (convert(varbinary(256),[cKeyword])),
CONSTRAINT [PK_KW_nKeyWord_Id] PRIMARY KEY CLUSTERED
(
[nKeyWord_Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
GO
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_KWH_nKeyWord_Id]') AND parent_object_id = OBJECT_ID(N'[dbo].[KeyWord_Hierarchy]'))
ALTER TABLE [dbo].[KeyWord_Hierarchy] DROP CONSTRAINT [FK_KWH_nKeyWord_Id]
GO
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_KWH_nParent_Id]') AND parent_object_id = OBJECT_ID(N'[dbo].[KeyWord_Hierarchy]'))
ALTER TABLE [dbo].[KeyWord_Hierarchy] DROP CONSTRAINT [FK_KWH_nParent_Id]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[KeyWord_Hierarchy]') AND type in (N'U'))
DROP TABLE [dbo].[KeyWord_Hierarchy]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[KeyWord_Hierarchy](
[nKWH_Id] [int] IDENTITY(1,1) NOT NULL,
[nKeyWord_Id] [int] NOT NULL,
[nParent_Id] [int] NULL,
[nKWHC_Id] [int] NULL,
CONSTRAINT [PK_KWH_nKwh_Id] PRIMARY KEY CLUSTERED
(
[nKWH_Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON),
CONSTRAINT [U_KWH_nPK] UNIQUE NONCLUSTERED
(
[nParent_Id] ASC,
[nKeyWord_Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
GO
ALTER TABLE [dbo].[KeyWord_Hierarchy] WITH CHECK ADD CONSTRAINT [FK_KWH_nKeyWord_Id] FOREIGN KEY([nKeyWord_Id])
REFERENCES [dbo].[KeyWords] ([nKeyWord_Id])
GO
ALTER TABLE [dbo].[KeyWord_Hierarchy] CHECK CONSTRAINT [FK_KWH_nKeyWord_Id]
GO
ALTER TABLE [dbo].[KeyWord_Hierarchy] WITH CHECK ADD CONSTRAINT [FK_KWH_nParent_Id] FOREIGN KEY([nParent_Id])
REFERENCES [dbo].[KeyWord_Hierarchy] ([nKWH_Id])
GO
ALTER TABLE [dbo].[KeyWord_Hierarchy] CHECK CONSTRAINT [FK_KWH_nParent_Id]
GO
SELECT @@TRANCOUNT returns 0
select nKeyword_Id
fromKeyWords k
where not exists (select * from KeyWord_Hierarchy kh where kh.nKeyWord_Id = k.nKeyWord_Id)
gives me one row, 120375, which I delicately copy and paste (because no one wants this to be due to a typo more than me right now :-)) into the following declaration:
DECLARE @nKeyword_IdVarINTEGER = 120375
SELECT * FROM KeyWord_Hierarchy kh (NOLOCK) where kh.nKeyWord_Id = @nKeyword_IdVar
returns 0 rows. So far, so good.
DECLARE @nKeyword_IdVarINTEGER = 120375
DECLARE @nErrorVarINTEGER = 0
BEGIN TRAN New_Tran
EXECUTE @nErrorVar = dbo.wadw_keywords
@cActionArg = 'D',
@nKeyword_IdArg = @nKeyword_IdVar OUTPUT
IF @nErrorVar <> 0
BEGIN
PRINT 'Boo'
ROLLBACK TRAN New_Tran
END
ELSE
BEGIN
PRINT 'Yay!'
COMMIT TRAN New_Tran
END
GO
This yields the FK violation error:
Msg 547, Level 16, State 0, Procedure waDw_Keywords, Line 60
The DELETE statement conflicted with the REFERENCE constraint "FK_KWH_nKeyWord_Id". The conflict occurred in database "IMS_Work", table "dbo.KeyWord_Hierarchy", column 'nKeyWord_Id'.
The statement has been terminated.
Boo
Here's the code for the above sproc:
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[waDw_Keywords]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[waDw_Keywords]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[waDw_Keywords]
@cActionArgCHAR(1),
@nKeyWord_IdArg INTEGEROUTPUT,
@cKeywordArg VARCHAR(255)= NULL,
@nCLT_IdArg INTEGER = NULL,
@nHitCountArg INTEGER = NULL,
@nParent_IdArgINTEGER= NULL,
@nKWHC_IdArgINTEGER= NULL
AS
BEGIN
SET NOCOUNT ON
/* These SETs are required for the proper operation of the computed column / unique index on Keywords. */
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET CONCAT_NULL_YIELDS_NULL ON
DECLARE @nResultCode INTEGER
DECLARE @nKWH_IdVarINTEGER
DECLARE @nKWHC_IdVarINTEGER
DECLARE @nParent_IdVarINTEGER
DECLARE @nCursorVarCURSOR
/* Remove double spaces in Keywords. */
WHILE CHARINDEX(' ', @cKeywordArg) > 0
SELECT @cKeywordArg = REPLACE(@cKeywordArg, ' ', ' ')
IF (@cActionArg = 'C')
BEGIN
EXECUTE @nResultCode = dbo.aDw_keywords
@cActionArg = @cActionArg,
@nKeyWord_IdArg = @nKeyWord_IdArg OUTPUT,
@cKeywordArg = @cKeywordArg,
@nCLT_IdArg = @nCLT_IdArg,
@nHitCountArg = @nHitCountArg
RETURN @nResultCode
END
ELSE IF @cActionArg = 'D'
BEGIN
DELETEdbo.Keywords
WHERE@nKeyWord_IdArg = @nKeyWord_IdArg
SELECT@nResultCode = @@ERROR
END
RETURN @nResultCode
END
GO
GRANT EXECUTE ON [dbo].[waDw_Keywords] TO [public] AS [dbo]
GO
(Yes, there's a cursor in that sproc, there are 2 other @cActionArgs that could be passed.)
Finally, running this works just fine.
declare @nKeyword_Id integer = 120375
deleteKeyWords
wherenKeyWord_Id = @nKeyword_Id