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.
是否可以将varchar列作为主键，其值为'a'和'a'，在MS SQL Server 2008中总是会出现这种错误“违反主键约束”。在Oracle中，不会出现任何错误。顺便说一句，我不是用这种方式实现的，我只是试图将数据从oracle迁移到sql server。
Regards
问候
6 个解决方案
#1
1
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.
您可以使用文本或ntext列，这取决于您导入的数据类型及其长度——这将保留空格。char会占用空间，所以可能不合适。
#2
4
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.
SQL-92标准规定，为了进行字符串比较，在进行比较之前，字符串被填充为相同的长度:通常，pad字符是空格。
Therefore 'a' and 'a ' compare EQUAL and this violates the PK constraint. http://support.microsoft.com/kb/316626
因此'a'和'a'比较相等，这违反了PK约束。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.
您可以使用varbinary而不是varchar，但是这也不能满足您的要求。
#3
0
use a datatype that doesn't strip trailing spaces.
使用不带尾随空格的数据类型。
#4
0
You might try storing as a varbinary, and then converting to varchar when you select.
您可以尝试以varbinary存储，然后在选择时转换为varchar。
#5
0
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.
我认为这可能与ANSI_PADDING (ANSI_PADDING)有关:但是我在这里的测试表明，对于PKs(也可能是唯一的索引，而不是尝试过的)来说，这并没有什么帮助。
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...
适用于非pk字段——也就是说，它保留了insert上的尾随空间，但出于某种原因，不在PKs上……
See :
看到的:
http://support.microsoft.com/kb/154886/EN-US/
http://support.microsoft.com/kb/154886/EN-US/
#6
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.
您可以向主键约束添加另一列，该约束保存oracle列中数据的长度。这将允许您导入数据并在需要时重新构造oracle数据——使用oracle数据的长度和microsoft表的长度，以增加在报告中显示的丢失空间。
#1
1
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.
您可以使用文本或ntext列，这取决于您导入的数据类型及其长度——这将保留空格。char会占用空间，所以可能不合适。
#2
4
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.
SQL-92标准规定，为了进行字符串比较，在进行比较之前，字符串被填充为相同的长度:通常，pad字符是空格。
Therefore 'a' and 'a ' compare EQUAL and this violates the PK constraint. http://support.microsoft.com/kb/316626
因此'a'和'a'比较相等，这违反了PK约束。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.
您可以使用varbinary而不是varchar，但是这也不能满足您的要求。
#3
0
use a datatype that doesn't strip trailing spaces.
使用不带尾随空格的数据类型。
#4
0
You might try storing as a varbinary, and then converting to varchar when you select.
您可以尝试以varbinary存储，然后在选择时转换为varchar。
#5
0
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.
我认为这可能与ANSI_PADDING (ANSI_PADDING)有关:但是我在这里的测试表明，对于PKs(也可能是唯一的索引，而不是尝试过的)来说，这并没有什么帮助。
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...
适用于非pk字段——也就是说，它保留了insert上的尾随空间，但出于某种原因，不在PKs上……
See :
看到的:
http://support.microsoft.com/kb/154886/EN-US/
http://support.microsoft.com/kb/154886/EN-US/
#6
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.
您可以向主键约束添加另一列，该约束保存oracle列中数据的长度。这将允许您导入数据并在需要时重新构造oracle数据——使用oracle数据的长度和microsoft表的长度，以增加在报告中显示的丢失空间。