CODEWITHSUNDEEP
sqlsql-serverdatabaset-sqlsql-server-2000
MAW74656
MAW74656

Reputation: 3539

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 sort of complex constraints. What does this one mean (in simple terms) and how can I get around it? Am I actually violating the constraint?

Upvotes: 1

Views: 169

Answers (4)

Martin Smith
Martin Smith

Reputation: 453288

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.

Upvotes: 1

KM.
KM.

Reputation: 103587

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.

Upvotes: 3

Yuck
Yuck

Reputation: 50855

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.

Upvotes: 0

Adriano Carneiro
Adriano Carneiro

Reputation: 58615

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]

Upvotes: -1

Related Questions