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
Valued Member
Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)

Group: General Forum Members
Points: 61 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
SSC Veteran
SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)

Group: General Forum Members
Points: 231 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
Valued Member
Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)

Group: General Forum Members
Points: 61 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
Valued Member
Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)

Group: General Forum Members
Points: 61 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
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5940 Visits: 5084
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
My blog: www.igormicev.com
Carlo Clausius
Carlo Clausius
Valued Member
Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)

Group: General Forum Members
Points: 61 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