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

Cannot insert duplicate key row in object with unique index on duplicate foreing key Expand / Collapse
Author
Message
Posted Wednesday, February 19, 2014 2:43 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 14, 2014 3:13 PM
Points: 21, Visits: 231
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
Post #1543233
Posted Wednesday, February 19, 2014 2:53 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 12:48 PM
Points: 123, Visits: 687
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
Post #1543237
Posted Wednesday, February 19, 2014 3:08 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 14, 2014 3:13 PM
Points: 21, Visits: 231
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.
Post #1543244
Posted Tuesday, February 25, 2014 3:28 PM This worked for the OP Answer marked as solution
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 14, 2014 3:13 PM
Points: 21, Visits: 231
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.
Post #1545154
Posted Tuesday, February 25, 2014 3:51 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 7:27 AM
Points: 2,951, Visits: 2,970
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,
SQL Server developer at Seavus
www.seavus.com
Post #1545156
Posted Tuesday, February 25, 2014 4:49 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 14, 2014 3:13 PM
Points: 21, Visits: 231
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.
Post #1545166
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse