Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Phantom FK violation Expand / Collapse
Author
Message
Posted Thursday, June 26, 2014 6:17 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 7:40 AM
Points: 430, Visits: 976
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!











Post #1586368
Posted Thursday, June 26, 2014 7:55 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:31 PM
Points: 2,389, Visits: 2,924
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’! **
Post #1586445
Posted Thursday, June 26, 2014 8:08 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 7:40 AM
Points: 430, Visits: 976
??? 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




Post #1586457
Posted Thursday, June 26, 2014 8:22 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 2:15 PM
Points: 13,077, Visits: 12,523
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 Moden's 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)
Post #1586466
Posted Thursday, June 26, 2014 8:54 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 7:40 AM
Points: 430, Visits: 976
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



Post #1586493
Posted Thursday, June 26, 2014 9:06 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 10:00 AM
Points: 5,408, Visits: 10,052
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
Post #1586503
Posted Thursday, June 26, 2014 9:07 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 2:15 PM
Points: 13,077, Visits: 12,523
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 Moden's 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)
Post #1586506
Posted Thursday, June 26, 2014 9:10 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 7:40 AM
Points: 430, Visits: 976
OMG!!!!!!

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

Thanks John! Good eye!




Post #1586508
Posted Thursday, June 26, 2014 9:20 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 10:00 AM
Points: 5,408, Visits: 10,052
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!
Post #1586518
Posted Thursday, June 26, 2014 9:23 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 7:40 AM
Points: 430, Visits: 976
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!!



Post #1586521
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse