SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Phantom FK violation


Phantom FK violation

Author
Message
schleep
schleep
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2715 Visits: 1405
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!



HanShi
HanShi
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8566 Visits: 3718
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.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
schleep
schleep
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2715 Visits: 1405
??? 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



Sean Lange
Sean Lange
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62165 Visits: 17954
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
schleep
schleep
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2715 Visits: 1405
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
from KeyWords 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_IdVar   INTEGER = 120375

SELECT * FROM KeyWord_Hierarchy kh (NOLOCK) where kh.nKeyWord_Id = @nKeyword_IdVar



returns 0 rows. So far, so good.



DECLARE @nKeyword_IdVar INTEGER = 120375
DECLARE @nErrorVar INTEGER = 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]
@cActionArg CHAR(1),
@nKeyWord_IdArg INTEGER OUTPUT,
@cKeywordArg VARCHAR(255) = NULL,
@nCLT_IdArg INTEGER = NULL,
@nHitCountArg INTEGER = NULL,
@nParent_IdArg INTEGER = NULL,
@nKWHC_IdArg INTEGER = 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_IdVar INTEGER
DECLARE @nKWHC_IdVar INTEGER
DECLARE @nParent_IdVar INTEGER
DECLARE @nCursorVar CURSOR

/* 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
DELETE dbo.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
delete KeyWords
where nKeyWord_Id = @nKeyword_Id



John Mitchell-245523
John Mitchell-245523
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34399 Visits: 16649
I think it's this in your stored procedure:
      DELETE   dbo.Keywords
WHERE @nKeyWord_IdArg = @nKeyWord_IdArg



That's going to delete the whole table (unless @nKeyWord_IdArg is null).

John
Sean Lange
Sean Lange
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62165 Visits: 17954
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. :-D

_______________________________________________________________

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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
schleep
schleep
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2715 Visits: 1405
OMG!!!!!!

Yikes. That's been in prod for years.... which means that sproc is never called by the application.

Thanks John! Good eye!



John Mitchell-245523
John Mitchell-245523
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34399 Visits: 16649
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!
schleep
schleep
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2715 Visits: 1405
The value is passed in the call to the sproc:

DECLARE @nKeyword_IdVar INTEGER = 120375
DECLARE @nErrorVar INTEGER = 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!!



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search