Primary key error Uppercase/lowercase

Hello,
I've got a problem with 2 tables, the first one has not constraint and is used as a data source, the second one as primary and foreign key constraints and is the destination.

In the first one the columns are :
TABLE PROMOTION :
PROMOTION_ID; PROMOTION_DESC;
16a;-20% discount;
16A;-30% discount;
AbC;no discount;
aBc; -90% discount;

The second table, TD_PROMOTION, has the same structure, but the column PROMOTION_ID column has a primary key constraint.
When i want to insert data from PROMOTION to TD_PROMOTION, i've got a primary_key constraint error, because it seems that the database makes no difference between the codes 16a and 16A and between AbC and aBc.
Is there a way to fix this problem ?

Thank you in advance,
Eric.

You can specify if SQL Server should be case sensitive or not using collation. In this instance the column must have a case sensitive collation in order for you to be able to specify any type of unique constraint on it. For example, the first example will fail whereas the second will work, notice the CI and CS for case insensitive and sensitive.

CREATE TABLE test1 (
col1 varchar(20) COLLATE Latin1_General_CI_AS PRIMARY KEY
)

INSERT INTO test1 VALUES ('ASD')
INSERT INTO test1 VALUES ('asd')

CREATE TABLE test2 (
col1 varchar(20) COLLATE Latin1_General_CS_AS PRIMARY KEY
)

INSERT INTO test2 VALUES ('ASD')
INSERT INTO test2 VALUES ('asd')

DROP TABLE test1
DROP TABLE test2

Collation can be set at the column or database level. If set at database level then all character columns without a collation specified adopt the database collation.

There are many collations available to you, search Books Online for a list of these.

Nick
DBA http://www.comoni.co.uk

|||Thank You, i exported my databases scripts, changed the collation CI to CS, and then recreated the databases, now it works fine !
Thank you !
sql