Unique constraint violation with single character insert

Question

I have a unique constraint on a SQL server 2008 database table, with the constraint containing 4 columns. While entering in some test data, I unexpectedly received a unique constraint violation. For the four columns I was just entering in a number, inserting the value set, incrementing the number, then repeating (i.e. (1,1,1,1) (2,2,2,2,)... etc.). The violation occurred when I got to 10. If I enter any number now, it will violate the constraint. I then tried it entering just the letter 'A', and tried to insert any words beginning with 'A', which led to a violation. Do I need to change something in my constraint, or this how they are actually supposed to work?

ALTER TABLE [dbo].[Table] ADD  CONSTRAINT [UC_Table_Column1_Column2_Column3_Column4] 
UNIQUE NONCLUSTERED 
(
    [Column1] ASC,
    [Column2] ASC,
    [Column3] ASC,
    [Column4] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF,    
IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON   
[PRIMARY]

What are definitions for the 4 "Column" fields? Do they allow for more than 1 character? — PM 77-1
VARCHAR(50) NOT NULL, VARCHAR(50) NOT NULL, INT NOT NULL, VARCHAR(25) NOT NULL — user2023116
I can't reproduce your problem. Please give the full definition of your table. Are there any other constraints on the table? Any triggers set? What is the full text of the error you receive? — Dave K
Incredibly dumb mistake on my part guys. Sorry about the waste of time. Thanks any ways for the help. — user2023116

sqlfool sqlfool · Accepted Answer · 2013-03-12T15:55:45

That should work just fine. What's the actual table definition? What's your collation?

-- Working Example
CREATE TABLE dbo.[Table](
      Column1 VARCHAR(50)
    , Column2 VARCHAR(50)
    , Column3 INT
    , Column4 VARCHAR(25)
);

ALTER TABLE [dbo].[Table] ADD  CONSTRAINT [UC_Table_Column1_Column2_Column3_Column4] 
UNIQUE NONCLUSTERED 
(
    [Column1] ASC,
    [Column2] ASC,
    [Column3] ASC,
    [Column4] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF,    
IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON   
[PRIMARY]

DECLARE @iteration INT = 0;

WHILE @iteration < 20
BEGIN
    INSERT INTO [dbo].[Table]
    SELECT @iteration, @iteration, @iteration, @iteration;

    SET @iteration += 1;
END;

SELECT * FROM [dbo].[Table];

Unique constraint violation with single character insert

1 Answers