Unique filtered index claims there is a duplicate, but there is not

Question

I have an unique filtered index that is not allowing a PO to be inserted and there is no duplicate to be found.

How can I allow POs to be inserted that are unique when the index thinks they are duplicates?

Table schema:

CREATE TABLE [dbo].[Orders]
(
    [Order_Num] [int] IDENTITY(1,1) NOT NULL,
    [PO_Num] [varchar](50) NOT NULL,
    [Concept] [varchar](50) NULL,
    [Lane] [varchar](256) NOT NULL,
    [OrderDate] [date] NULL,
    [ShipDate] [date] NULL,
    [PlannedDeliveryDate] [date] NULL,
    [CreateDate] [date] NOT NULL,
    [EditDate] [date] NULL,
    [Status] [varchar](1) NOT NULL,

    CONSTRAINT [PK_Orders] 
        PRIMARY KEY CLUSTERED ([Order_Num] ASC)
                WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                      IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                      ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Orders] 
    ADD CONSTRAINT [DF_Orders_Status] DEFAULT ('O') FOR [Status]
GO

ALTER TABLE [dbo].[Orders] 
    ADD CONSTRAINT [DF_Orders_MGProcessFlag]  DEFAULT ('N') FOR [MGProcessFlag]
GO

Unique index on the table:

CREATE UNIQUE NONCLUSTERED INDEX [NCI_UNQ_001_PO_Concept_Status] 
ON [dbo].[Orders] ([PO_Num] ASC, [Concept] ASC, [Status] ASC)
WHERE ([Status] = 'O')
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
      SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, 
      ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, 
      OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

I have searched for two particular POs that have come in and I am holding them in a staging table, they pass my checks and should flow in to this table, but the unique index throws this error:

Cannot insert duplicate key row in object 'dbo.Orders' with unique index 'NCI_UNQ_001_PO_Concept_Status'. The duplicate key value is (25154153677, SMS, O).

BUT, that PO is not in the Orders table at all.

I have run the following to check for dupes, and there are some, but NONE are Status 'O'. 'C' and 'X' (Closed and Cancelled) are OK to have dupes.

SELECT PO_Num, Concept, [Status], COUNT(*)
FROM Orders
GROUP BY PO_Num, Concept, [Status]
HAVING COUNT(*) > 1;

I cannot find these two POs in the Orders table at all.

What is going on here? Do I have my filtered index constructed incorrectly?

The only thing I can think of is that maybe I should drop the Status from the columns since it is in the filter.

I really can't have duplicate open Orders in this table. How can I fix this?

Remove "HAVING COUNT(*) > 1". A UNIQUE index will not allow a duplicate insertion so there will never be a "COUNT > 1" and that one existing unique row is also filtered out. — radarbob
– radarbob, Commented Nov 25 at 22:08
I did this, no joy. I did a plain SELECT on the 2 POs, no joy. They simply are not in the table, at all. — Ken Watson
– Ken Watson, Commented Nov 25 at 22:22
If you are inserting into dbo.Orders using INSERT...SELECT FROM <your-staging-table>, run the query against the staging table to make sure it doesn't have the dups. — Dan Guzman
– Dan Guzman, Commented Nov 25 at 22:33
If a primary key is for defining uniqueness of records (table rows) in context of the business/data model, then a "one-up" primary key is useless (and unusable as a practical matter) virtually all the time. That 3-column index seems to be a de-facto key. — radarbob
– radarbob, Commented Nov 25 at 22:37
Side note: varchar(1) entertaining, did you think you might also store an empty string there? — Charlieface
– Charlieface, Commented Nov 26 at 3:11

Randy in Marin · Accepted Answer · 2025-11-29 06:35:54Z

0

See if it's in this table instead of the table defined in the default schema. If using transactions, check in the same session in case there are uncommitted changes.

SELECT PO_Num, Concept, Status
FROM dbo.Orders
WHERE PO_Num = '25154153677' AND Concept = 'SMS' AND Status = 'O'

answered 20 hours ago

Randy in Marin

1,1436 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Unique filtered index claims there is a duplicate, but there is not

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related