February 19, 2014 at 2:43 pm
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
February 19, 2014 at 2:53 pm
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
February 19, 2014 at 3:08 pm
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.
February 25, 2014 at 3:28 pm
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.
February 25, 2014 at 3:51 pm
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
February 25, 2014 at 4:49 pm
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.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply