June 26, 2014 at 6:17 am
I use a proc to delete a row from a PK table, based on a query of "get the PK ids that don't exist in the FK table". The proc fails with an FK violation. The proc does a simple "delete from PK table where pk_id = n"
So I delete the PK row manually (delete from PK table where pk_id = n). No complaints. Fine -- well not really, but...
Verified that the row does not exist in either the PK nor FK tables.
So I:
- DBCC'ed, no errors reported.
- Dropped and re-created the FK WITH CHECK. No complaints.
- Call the proc again just for good times, and still get the FK violation?????
- Put the delete statement in the sproc into a try...catch block to the sure it was actually failing at that point, confirmed.
There are no triggers on either table.
There must be a SNAFU somewhere. Can someone point me in the right direction?
Thanks!
June 26, 2014 at 7:55 am
Can you give us the exact code of the procedure and the exact code you execute from the query window, instead of a descriptive text of your action? If possible also give us some sample data (CREATE tables + sample rows) in which we can reproduce your problem.
June 26, 2014 at 8:08 am
??? Maybe I didn't get enough sleep last night, but... I don't believe it should be possible to provide sample data to reproduce this scenario.
To paraphrase my post, "I'm getting a FK violation on a delete from the PK table where no row exists in the FK table that violates the FK constraint."
The manual and procedure code is essentially identical:
Proc: DELETE Keywords WHERE nKeyword_Id = @nKWIdVar
Manual: DELETE Keywords WHERE nKeyword_Id = 123075
June 26, 2014 at 8:22 am
schleep (6/26/2014)
??? Maybe I didn't get enough sleep last night, but... I don't believe it should be possible to provide sample data to reproduce this scenario.To paraphrase my post, "I'm getting a FK violation on a delete from the PK table where no row exists in the FK table that violates the FK constraint."
The manual and procedure code is essentially identical:
Proc: DELETE Keywords WHERE nKeyword_Id = @nKWIdVar
Manual: DELETE Keywords WHERE nKeyword_Id = 123075
I understand the challenges of not being able to post sample data since this seems to be impossible. How about if you post the ddl for the tables and the actual code you are running.
Do you have any open transactions that is causing locking? Try running a select statement with(NOLOCK) to see if the row is still there but has been deleted and left hanging in a transaction.
There are so many things that could be causing this. It is really tough to troubleshoot this remotely.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 26, 2014 at 8:54 am
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
June 26, 2014 at 9:06 am
I think it's this in your stored procedure:
DELETEdbo.Keywords
WHERE@nKeyWord_IdArg = @nKeyWord_IdArg
That's going to delete the whole table (unless @nKeyWord_IdArg is null).
John
June 26, 2014 at 9:07 am
You are attempting to delete the entire Keywords table because of this.
DELETE dbo.Keywords
WHERE @nKeyWord_IdArg = @nKeyWord_IdArg
You have the same variable on both side of the predicate. Pretty sure that all you need to do is remove the @ from one of them. 😉
There are definitely some performance improvements you could make in here if you are interested.
--EDIT--
Looks like John beat me to it. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 26, 2014 at 9:10 am
OMG!!!!!!
Yikes. That's been in prod for years.... which means that sproc is never called by the application.
Thanks John! Good eye!
June 26, 2014 at 9:20 am
Actually, I'm puzzled. Since the value of @nKeyWord_IdArg isn't set anywhere, I'd expect it to be NULL and therefore no deletion should be attempted. Unless ANSI_NULLS is set to OFF?
John
Edit - forget that. I see what's happened. The parameter is declared as an output parameter but used as an input parameter, and the value is set when the stored procedure is called. Very confusing!
June 26, 2014 at 9:23 am
The value is passed in the call to the sproc:
DECLARE @nKeyword_IdVarINTEGER = 120375
DECLARE @nErrorVarINTEGER = 0
BEGIN TRAN New_Tran
EXECUTE @nErrorVar = dbo.wadw_keywords
@cActionArg = 'D',
@nKeyword_IdArg = @nKeyword_IdVar OUTPUT
So it was evaluating to DELETE Keywords WHERE 120375 = 120375. Gah!!
June 26, 2014 at 9:36 am
Sean, I'd be happy to hear any suggestions re: perf improvement.
June 26, 2014 at 9:44 am
schleep (6/26/2014)
Sean, I'd be happy to hear any suggestions re: perf improvement.
The first suggestion is to remove the while loop that is replacing multiple spaces with one. Jeff Moden has a very clever of way doing this with no loop.
http://www.sqlservercentral.com/articles/T-SQL/68378/[/url]
The second would be to get rid of the cursor if possible. Of course we only have a portion of the actual code since the cursor is declared but never referenced again.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 26, 2014 at 9:57 am
WHILE: Agreed. However, this sproc is called for Inserts and updates an average of 10x/day, so the perf hit is probably not noticeable.
As for the cursor, there are 2 related sprocs called for each row in hierarchy affected by a change in a keyword, each having some rather complex logic.
(I will NOT share the nested! cursor! solution we use to retrieve and present the keywords and data to which they are linked. But it generally runs in under 1 second, 20K times a day).
Thanks!
P
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply