January 17, 2011 at 2:19 pm
<code>
Server Error in '/' Application.
--------------------------------------------------------------------------------
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_tbl_Favorites_tbl_Profile". The conflict occurred in database "omegalove", table "dbo.tbl_Profile", column 'ProfileID'.
The statement has been terminated.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_tbl_Favorites_tbl_Profile". The conflict occurred in database "omegalove", table "dbo.tbl_Profile", column 'ProfileID'.
The statement has been terminated.
CREATE TABLE [dbo].[tbl_Favorites](
[UserNameID] [int] NOT NULL,
[FavoriteUserNameID] [int] NOT NULL,
[TimeStamp] [datetime] NOT NULL CONSTRAINT [DF_Favorites_TimeStamp] DEFAULT (getdate()),
CONSTRAINT [PK_Favorites] PRIMARY KEY CLUSTERED
(
[UserNameID] ASC,
[FavoriteUserNameID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tbl_Favorites] WITH CHECK ADD CONSTRAINT [FK_tbl_Favorites_tbl_Profile] FOREIGN KEY([UserNameID])
REFERENCES [dbo].[tbl_Profile] ([ProfileID])
GO
ALTER TABLE [dbo].[tbl_Favorites] CHECK CONSTRAINT [FK_tbl_Favorites_tbl_Profile]
GO
ALTER TABLE [dbo].[tbl_Favorites] WITH CHECK ADD CONSTRAINT [FK_tbl_Favorites_tbl_Profile1] FOREIGN KEY([FavoriteUserNameID])
REFERENCES [dbo].[tbl_Profile] ([ProfileID])
GO
ALTER TABLE [dbo].[tbl_Favorites] CHECK CONSTRAINT [FK_tbl_Favorites_tbl_Profile1]
and here is the stored proc what should I do.
ALTER PROCEDURE [dbo].[prc_Favorites_Save]
(
@UserNameIDint,
@FavoriteUserNameIDint,
@MaxFavoriteUsers int
)
AS
IF ((SELECT COUNT(*) FROM tbl_Favorites
WHERE @UserNameID = UserNameID AND @FavoriteUserNameID = FavoriteUserNameID) <> 0)
SELECT 2
ELSE IF ( (SELECT COUNT(*) FROM tbl_Favorites
WHERE @UserNameID = UserNameID) = @MaxFavoriteUsers)
SELECT 3
ELSE BEGIN
INSERT INTO tbl_Favorites
(UserNameID, FavoriteUserNameID)
VALUES
(@UserNameID,@FavoriteUserNameID)
SELECT 1
END
</code>
January 17, 2011 at 3:27 pm
Try this. Instead of the COUNT statement, use an EXISTS statement. It will perform much better.
IF EXISTS (SELECT * from dbo.tbl_WhateverItWas WHERE Val1 = @Param1 AND Val2 = @Param2)...
Of course, the real problem is you tried to insert a value that didn't exist in the lookup table. You need to check your data.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply