March 23, 2009 at 3:16 pm
Hi,
I am using the following code for SP.
And its is errorring as ''INSERT statement conflicted with COLUMN FOREIGN KEY SAME TABLE constraint 'fk_tEDUCATION_CODES1'. The conflict occurred in database 'GroupOTest', table 'tEDUCATION_CODES', column 'EducationCodeIdNo'.
Code is...
ALTER PROCEDURE [dbo].[sp_CUSTOM_SaveOtherMajor]
@MajorCode as int = NULL,
@EducationCodeDesc as nvarchar(40)
AS
DECLARE @DUPE as int
DECLARE @INC as int
DECLARE @CODE as int
BEGIN
SET NOCOUNT ON;
-- IF (@MajorCode = 0)
SET @DUPE = (select max(educationcodeidno) from teducation_codes)
-- + 1
SET @INC = CAST(@DUPE as int) + 1
INSERT INTO tEDUCATION_CODES (
EDucationCodedomainIdno,
EducationCode,
EducationCodeType,
EducationCodeDescription,
Educationcodefromeffectdate,
Educationcodetoeffectdate,
Languageid,
EducationcodeDefaultCodeind)
VALUES(
@INC,
@EducationCodeDesc,
'J',
@EducationCodeDesc,
'1900-01-02',
'3000-01-01',
'en-us',
'1')
END
SELECT @@IDENTITY AS EducationCodeidno
SET @CODE = @@IDENTITY
SELECT @CODE as EducationCodeidno
SELECT @INC
Please help me to solve this.. tell me what am doing wrong.
March 23, 2009 at 6:06 pm
Hi,
it looks like you're having a self-referencing foreign key (foreign key [EDucationCodedomainIdno] references the prim key [educationcodeidno] of the same table).
Obviously, there has been a delete statement that affected the max([educationcodeidno]) or a failed insert. A delete statement won't decrement the [educationcodeidno]. A failed insert will still increment. Thereby, your "select max(educationcodeidno) from teducation_codes" statement gives you the highest number that's currently stored in the educationcodeidno column. But that's not always equal to the (internal) max(identity) value. Even max($IDENTITY) won't give you this internal number.
Instead of trying to insert the (unknown) value from [educationcodeidno] into [EDucationCodedomainIdno], you could change the [EducationCodedomainIdno] column to a computed column (=educationcodeidno). Therewith, you won't have to worry about getting both values identical. You won't even have to calculate the value for @INC anymore nor do you have to include it in the insert statement. You also can get rid of the max(educationcodeidno) select.
It would be even better not to use the self-referencing foreign key in that table at all, since both columns will show identical data (redundancy).
Why don't you use only [educationcodeidno] for references and remove the [EDucationCodedomainIdno] column?
March 24, 2009 at 7:31 am
Hi ,
Thanks alot for your good explanation..:)
I am actually using Krono's database(we bought the site and databbse from krono's).. so everything is already developed. I am not sure whther i can change table structure or not.
So, Is there anyway around?
I will try to see if i can modify the structure of the table..
So my procedure is not correct or its not supporing the existing table strucuture?
March 24, 2009 at 10:39 am
your procedure is basically correct and supports the existing table structure. But the table structure ist the problem. 😀
If you cannot alter the table structure you have several ways to work around (all of them kind'a "dirty" - but that's the price you have to pay if you cannot change the table structure):
Two options I know of:
1) Insert the value of @DUPE into the column EDucationCodedomainIdno and do an update afterwards to that column with the value of @@IDENTITY for the row with EducationCodeidno = @@IDENTITY.
This would mean a 100% double-touch of each inserted row, which I wouldn't really recommend
2) Use a TRY .. CATCH Block around the insert statement and force the current identity value to the max selected in case of an identity error. I'm not sure what side effects the second scenario would have on your database or in general. Maybe some of the gurus around do know a better way to deal with it...
Instead of forcing the identity value you could also write the insert values into an error log and work from there (which would require some sort of immediate maintenance, since every insert will fail starting with the first mismatch.)
Attached is a code snippet to reset the identity column following by a retry of the insert statement.
BEGIN TRY
SET @DUPE = (select max(educationcodeidno) from teducation_codes)
-- + 1
SET @INC = CAST(@DUPE as int) + 1
INSERT INTO tEDUCATION_CODES (
EDucationCodedomainIdno,
EducationCode,
EducationCodeType,
EducationCodeDescription,
Educationcodefromeffectdate,
Educationcodetoeffectdate,
Languageid,
EducationcodeDefaultCodeind)
VALUES(
@INC,
@EducationCodeDesc,
'J',
@EducationCodeDesc,
'1900-01-02',
'3000-01-01',
'en-us',
'1')
SET @CODE = @@IDENTITY
END TRY
BEGIN CATCH
IF ERROR_NUMBER() = 547 -- Error number of constraint conflict
BEGIN
DBCC CHECKIDENT ('teducation_codes', RESEED, @DUPE) -- forces the current identity value to the max selected
-- retry to insert the values
INSERT INTO tEDUCATION_CODES (
EDucationCodedomainIdno,
EducationCode,
EducationCodeType,
EducationCodeDescription,
Educationcodefromeffectdate,
Educationcodetoeffectdate,
Languageid,
EducationcodeDefaultCodeind)
VALUES(
@INC,
@EducationCodeDesc,
'J',
@EducationCodeDesc,
'1900-01-02',
'3000-01-01',
'en-us',
'1')
-- important note: addtl. error handling required, if this insert fails e.g. due to field type mismatch
END
END CATCH
March 24, 2009 at 10:45 am
WOW.. its a good solution.. I just tried the first one for now it worked.. but if you actually dont recommend that then i will get back to second way.
Anyway thanks alot for your help
March 24, 2009 at 11:14 am
Like I said, both of them are a kind of "dirty"...
If the data volume you need to handle allows the first option then you probably should stay with that solution, since you're not "playing" with values of an identity column. The reason I don't recommend it is the fact of the 100% double-touch of each and every single row.
The socond version will use just one insert (without touching the CATCH block) as long as there is no foreign key conflict.
My recommendation was primarily driven by performance aspects under the assumption that the foreign key conflict happens just once in a while.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply