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


Cannot insert duplicate key row in object with unique index on duplicate foreing key


Cannot insert duplicate key row in object with unique index on duplicate foreing key

Author
Message
Carlo Clausius
Carlo Clausius
SSC-Enthusiastic
SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)

Group: General Forum Members
Points: 127 Visits: 234
Hi

I'm getting the "SQL Server Cannot insert duplicate key row in object with unique index" error during an insert between a table and another that supplies multiple instances of its id as a foreign Key. Here are the details.

CREATE TABLE [dbo].[TBL_DFQ_PARTS](
[id] [int] IDENTITY(1,1) NOT NULL,
[item_nbr] [varchar](255) NULL,
[fk_mfg_part] [int] NOT NULL,
[fk_commodity_type] [int] NOT NULL CONSTRAINT [DF_TBL_DFQ_PARTS_fk_commodity_type] DEFAULT ((0)),
[mod_date] [datetime] NOT NULL CONSTRAINT [DF_TBL_DFQ_PARTS_mod_date] DEFAULT (getdate()),
[coda_nbr] [varchar](255) NULL CONSTRAINT [DF_TBL_DFQ_PARTS_coda_nbr] DEFAULT (N''),
[requires_dma] [bit] NOT NULL,
CONSTRAINT [PK_TBL_DMA_MAIN] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[TBL_DFQ_MFG_PART_NBR](
[id] [int] IDENTITY(0,1) NOT NULL,
[fk_mfg_name] [int] NOT NULL,
[mfg_part_nbr] [varchar](250) NOT NULL,
[fk_mfg_part_data_sheet] [int] NOT NULL,
[mod_date] [datetime] NOT NULL CONSTRAINT [DF_TBL_DFQ_MFG_PART_NBR_mod_date] DEFAULT (getdate()),
CONSTRAINT [PK_TBL_DFQ_MFG_PART_NBR] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


I have a stored procedure which looks for an instance of a mfg_part_nbr value in TBL_DFQ_MFG_PART_NBR. If it's not found, it inserts the new value and returns the record's [id] which in turn is included in the insert into TBL_DFQ_PARTS.fk_mfg_part field. If the mfg_part_nbr value already exists, then it returns its [id] straight up. So far so good.

This is a snap shot of some data in TBL_DFQ_PARTS.
id - item_nbr - fk_mfg_nb - fk_commodity_type
66 - 101-0501 - 62 - 0
67 - 137-0R05-Z - 63 - 0
68 - 153-0201-Z - 64 - 0

Now I want to insert this line...

item_nbr - mfg_nbr
172-0201-Z - ERJ-14YJ201U (RK73B3ATTD201J)

which translates into this...
69 - 172-0201-Z - 64

The new Part number (172-0201-Z) has the same Mfg Part number (fk_mfg_nbr = 64) as another item in the TBL_DFQ_PARTS (id=68).

When I tried to insert this into the table TBL_DFQ_PARTS I get this error:

[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert duplicate key row in object 'dbo.TBL_DFQ_PARTS' with unique index 'IX_TBL_DFQ_PARTS'.

I tried entering the data directly into the table and I get the same error. I don't know why it should bark at the duplicate fk_mfg_nbr value. Any ideas?

Thanks

Carlo
stormsentinelcammy
stormsentinelcammy
Mr or Mrs. 500
Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)

Group: General Forum Members
Points: 525 Visits: 807
looks like there is a constraint to not allow duplicates in the table that is referring to fk_mfg_nb in your output in your comment, its a unique index so thus its not allowing another 64 in that column in that table, if it were 65 that you were inserting it would allow it...option is to include another column that will make it unique or change the value to 65 to have the ability to insert it
Carlo Clausius
Carlo Clausius
SSC-Enthusiastic
SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)

Group: General Forum Members
Points: 127 Visits: 234
Can't change the value to 65 as it's the [id] from the other table TBL_DFQ_MFG_PART_NBR. The need then is to have the field fk_mfg_nbr allow duplicates.
Carlo Clausius
Carlo Clausius
SSC-Enthusiastic
SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)

Group: General Forum Members
Points: 127 Visits: 234
Turns out that SQL Server, by default, sets indexed fields to allow only unique values. To check this, open the table in Design and select "Manage Indexes and Keys" option. Select the index/key listed and check the appropriate Yes/No drop down for the "Is Unique" property.
Igor Micev
Igor Micev
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10418 Visits: 5157
The change of a unique to non-unique index means changing the business logic, so make a double check if you're going with this.

Igor Micev,
My blog: www.igormicev.com
Carlo Clausius
Carlo Clausius
SSC-Enthusiastic
SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)

Group: General Forum Members
Points: 127 Visits: 234
Noted. In this case, it's a standard Foreign key relationship, like having the id from a country table as a fk in an address table.
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