5

I have a table with a column that has a unique constraint on it. My data has meaningful trailing white space, so 'foo' and 'foo ' are meaningfully different. However, if I try to add a row with each value it violates the unique constraint on the column.

If the column is a primary key, I get this error:

Violation of PRIMARY KEY constraint 'PK_Foo'. Cannot insert duplicate key in object 'dbo.Foo'. The duplicate key value is (foo ).

If the column has a unique constraint, I get this error:

Cannot insert duplicate key row in object 'dbo.Foo' with unique index 'IX_Foo'. The duplicate key value is (foo ).

Below is the script that I would like to pass.

SET ANSI_PADDING ON  -- this has no effect, I've included it to preempt the suggestion
GO

CREATE TABLE [dbo].[Foo](
    [Bar] [varchar](50) NOT NULL,
    [Baz] [varchar](50) NOT NULL,
 CONSTRAINT [PK_Foo] PRIMARY KEY CLUSTERED 
(
    [Bar] ASC
)
) ON [PRIMARY]

GO

CREATE UNIQUE NONCLUSTERED INDEX [IX_Foo] ON [dbo].[Foo]
(
    [Baz] ASC
)
GO

INSERT INTO [dbo].[Foo] (Bar, Baz) VALUES ('foo', '1')
INSERT INTO [dbo].[Foo] (Bar, Baz) VALUES ('foo ', '2')

INSERT INTO [dbo].[Foo] (Bar, Baz) VALUES ('1', 'foo')
INSERT INTO [dbo].[Foo] (Bar, Baz) VALUES ('2', 'foo ')

SET ANSI_PADDING OFF
Stefan Moser
  • 6,663
  • 9
  • 35
  • 48
  • 1
    In short, you can't. 'foo' = 'foo '. When doing a comparison trailing spaces are ignored. – Sean Lange Oct 26 '16 at 18:22
  • from Microsoft: The SET ANSI_PADDING setting does not affect whether SQL Server pads strings before it compares them. SET ANSI_PADDING only affects whether trailing blanks are trimmed from values being inserted into a table, so it affects storage but not comparisons – FLICKER Oct 26 '16 at 18:27

0 Answers0