4

Is it possible to have a varchar column as a primary key with values like 'a ' and 'a', is gives always this error "Violation of PRIMARY KEY constraint" in MS SQL Server 2008. In Oracle dons't give any error. BTW I'm not implementing this way I'm only trying to migrate the data from oracle to sql server.

Regards

cacaupt
  • 53
  • 1
  • 5

6 Answers6

5

The SQL-92 standard dictates that for character string comparison purposes, the strings are padded to be the same length prior to comparison: typically the pad character is a space.

Therefore 'a' and 'a ' compare EQUAL and this violates the PK constraint. http://support.microsoft.com/kb/316626

I could find nothing to indicate this behaviour has changed since then.

You may get away with using varbinary instead of varchar but this may not do what you want either.

Steven
  • 51
  • 1
  • 2
2

You can use a text or ntext column, which one depends on the kind of data you are importing and its length - this will preserve spaces. char will pad spaces, so may not be suitable.

Oded
  • 489,969
  • 99
  • 883
  • 1,009
  • 1
    text and ntext are deprecated, and char will pad with blanks if you don't provide them. – RickNZ Dec 30 '09 at 11:53
  • 2
    `text` and `ntext` may be deprecated, but for a one time import to a temporary table I don't see a problem. – Oded Dec 30 '09 at 12:02
  • To char, 'a' and 'a ' will both pad to 'a '. To varchar, they both get changed to 'a' – MartW Dec 30 '09 at 12:02
  • 3
    Oh, and text and ntext are not indexable and therefore cannot be used as primary keys. – MartW Dec 30 '09 at 12:09
  • I met a similar problem, and used ntext as loading target, then alter the column type to nvarchar. it seems MSSQL 2014 allows text data type to be primary key though. – athos Dec 16 '14 at 07:53
1

I thought this might have something to do with ANSI_PADDING: but my testing here, indicates that for PKs (possibly UNIQUE INDEXES as well, not tried) this still doesn't help unfortunately.

So:

SET ANSI_PADDING ON

Works for non-PK fields - that is, it preserves the trailing space on the insert, but for some reason not on PKs...

See :

http://support.microsoft.com/kb/154886/EN-US/

monojohnny
  • 5,894
  • 16
  • 59
  • 83
  • thanks I've see that to, what I don't know is if there is an option form changing the way(with trailing pad or not) sql server compare unique keys... – cacaupt Dec 30 '09 at 12:47
0

use a datatype that doesn't strip trailing spaces.

just somebody
  • 18,602
  • 6
  • 51
  • 60
  • Such as what? And any such datatypes that treat content as string rather than opaque binary data? – binki Jan 18 '17 at 03:25
0

You might try storing as a varbinary, and then converting to varchar when you select.

RickNZ
  • 18,448
  • 3
  • 51
  • 66
0

You could add another column to your primary key constraint which holds the length of the data in the oracle column. This will allow you to import the data and to reconstruct the oracle data when you need to - with a view that uses the length of the oracle data along with the length in the microsoft table to add back the missing spaces for display in reports etc.

Mark Staniford
  • 1
  • 1