How am I violating this constraint?

I have this constraint in a table:

CREATE TABLE [dbo].[InventoryLocations]
(
    [recid] [int] IDENTITY(1,1) NOT NULL,
    [LocItemNumber] [char](16) NOT NULL,
    [WareHouse] [char](2) NOT NULL,
    [Aisle] [char](3) NOT NULL,
    [Slot] [char](3) NOT NULL,
    [locLevel] [char](2) NOT NULL,
    [Bin] [char](2) NOT NULL,
    [Extra] [char](2) NOT NULL,
    [LocNumber] [char](2) NOT NULL,
    [RollNumber] [char](20) NOT NULL,
    [QuickRoll] [int] NOT NULL,
    [SkidNumber] [char](15) NOT NULL,
    [RollsInStock] [int] NOT NULL,
    [LocQtyOnHand] [float] NOT NULL,
    [LocQtyOnOrder] [float] NOT NULL,
    [LocQtyCommited] [float] NOT NULL,
    [TotalReceived] [float] NOT NULL,
    [TotalIssued] [float] NOT NULL,
    [TotalDollars] [float] NOT NULL,
    [Capacity] [float] NOT NULL,
    [AvailableSpace] [float] NOT NULL,
    [bkey0] [char](30) NULL,
    [bkey1] [char](30) NULL,
    [bkey2] [char](30) NULL,
    [bkey3] [char](14) NULL,
    [LastPhysicalCountDate] [datetime] NULL,
    [LastCycleCountDate] [datetime] NULL,
    [EnteredBy] [varchar](50) NULL,
    [EnteredDateTime] [datetime] NULL,
CONSTRAINT [IX_InventoryLocations_1] UNIQUE NONCLUSTERED 
(
    [LocItemNumber] ASC,
    [WareHouse] ASC,
    [Aisle] ASC,
    [Slot] ASC,
    [locLevel] ASC,
    [Bin] ASC,
    [Extra] ASC,
    [RollNumber] ASC,
    [SkidNumber] ASC
));

And when trying to insert the following rows, I get the error message. Inside a cursor which populates the variables.

INSERT INTO [AVANTISERVER\NCL_MASTER].[Avanti].[dbo].[InventoryLocations](LocItemNumber, WareHouse, Aisle, Slot, locLevel, Bin, Extra, RollNumber, LocQtyOnHand, SkidNumber)
            SELECT @item, 'F', 'L', 'E', 'X', 'O', @seq, @seq, @qty, @seq
            FROM FI_CurrentReceiptData CR

Which works out to:

VALUES('MW1', 'F', 'L', 'E', 'X', 'O', 0, 0, 10, 0)
VALUES('MW1', 'F', 'L', 'E', 'X', 'O', 1, 1, 10, 1)

Msg 2627, Level 14, State 2, Line 34 Violation of UNIQUE KEY constraint 'IX_InventoryLocations_1'. Cannot insert duplicate key in object 'InventoryLocations'.

Please help me to understand why I cannot make these insertions? I am not experienced with these sor开发者_JAVA百科t of complex constraints. What does this one mean (in simple terms) and how can I get around it? Am I actually violating the constraint?

do you already have rows in the table that conflict with the data from your question that you are trying to insert?

EDIT after comments and question edit
add this:

put some prints in your loop to see what values you're inserting. I don't think SQL Server will issue an error unless you are trying to insert dups.

All of the items in your SELECT list are constant expressions.

INSERT INTO [AVANTISERVER\NCL_MASTER].[Avanti].[dbo].[InventoryLocations](LocItemNumber, WareHouse, Aisle, Slot, locLevel, Bin, Extra, RollNumber, LocQtyOnHand, SkidNumber)
            SELECT @item, 'F', 'L', 'E', 'X', 'O', @seq, @seq, @qty, @seq
            FROM FI_CurrentReceiptData CR

I'm not sure why you expect it to differ between rows but in any event it won't. Perhaps you meant to reference a column rather than a variable somewhere.

As you say in the comments these variables are being updated in a cursor presumably your SELECT is returning multiple rows FROM FI_CurrentReceiptData. Comment out the Insert and look at the results and see how many rows are returned. Or just remove FROM FI_CurrentReceiptData CR entirely as the SELECT doesn't use anything from it.

Without knowing the names of the columns you're trying to INSERT, just by the number of columns listed in your constraint (9) it looks like each row must be completely unique in the table InventoryLocations. You're inserting (10) columns. Your constraint is defined wrong based on the data you want to add.

EDIT: After your table definition edits, you still have too many columns listed in the UNIQUE CONSTRAINT which is causing the INSERT failure. It would really help if you listed the column names you're inserting to.

EDIT: You are inserting hard-coded values into columns listed in your constraint. Since they will always be the same the constraint is violated and you see the error you posted. You need to either vary the data going into the table or relax the constraint by removing some of the columns.

You have to let us know the first part of the INSERT, not only the values. However, it's easy to tell that what is happening, basically, is that you are trying to insert a record that will violate [IX_InventoryLocations_1] which states that no 2 records on the table can have the same values for the following fields:

[LocItemNumber]
[WareHouse]
[Aisle]
[Slot]
[locLevel]
[Bin]
[Extra]
[RollNumber]
[SkidNumber]