pk & trailing spaces

I have a situation where the primary key is:
user_id char(20) not null
user_cd char(2) not null
allows users to store trailing spaces, which in turn makes the key not unique.
For example it allows the following, one with spaces one without:
ernie 01
ernie 01
This in turn causes processing problems in the app.
How do I fix this problem? BTW, ANSI_PADDING is turned off.
Thanks,
GracieUse VARCHAR not CHAR.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:DC0DFC0D-1017-40CD-A5C3-C6BD70224E62@.microsoft.com...
> I have a situation where the primary key is:
> user_id char(20) not null
> user_cd char(2) not null
> allows users to store trailing spaces, which in turn makes the key not
unique.
> For example it allows the following, one with spaces one without:
> ernie 01
> ernie 01
> This in turn causes processing problems in the app.
> How do I fix this problem? BTW, ANSI_PADDING is turned off.
> Thanks,
> Gracie|||What if I can't?
"gracie" wrote:
> I have a situation where the primary key is:
> user_id char(20) not null
> user_cd char(2) not null
> allows users to store trailing spaces, which in turn makes the key not unique.
> For example it allows the following, one with spaces one without:
> ernie 01
> ernie 01
> This in turn causes processing problems in the app.
> How do I fix this problem? BTW, ANSI_PADDING is turned off.
> Thanks,
> Gracie|||Char is a fixed length datatype, it will always pad spaces to length of data declaration with
spaces. Did you mean varchar?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:1CDF624A-3879-4BD9-81F1-01A4043E17D7@.microsoft.com...
> What if I can't?
> "gracie" wrote:
>> I have a situation where the primary key is:
>> user_id char(20) not null
>> user_cd char(2) not null
>> allows users to store trailing spaces, which in turn makes the key not unique.
>> For example it allows the following, one with spaces one without:
>> ernie 01
>> ernie 01
>> This in turn causes processing problems in the app.
>> How do I fix this problem? BTW, ANSI_PADDING is turned off.
>> Thanks,
>> Gracie|||Is it by design that you allow users to put trailing spaces? If not make a
small change in the insert statement on the web page sql statement...
RTRIM(user_id)
Am suggesting this only if you can't change the datatype to varchar.
Thanks
GYK
"gracie" wrote:
> What if I can't?
> "gracie" wrote:
> > I have a situation where the primary key is:
> >
> > user_id char(20) not null
> > user_cd char(2) not null
> >
> > allows users to store trailing spaces, which in turn makes the key not unique.
> >
> > For example it allows the following, one with spaces one without:
> >
> > ernie 01
> > ernie 01
> >
> > This in turn causes processing problems in the app.
> >
> > How do I fix this problem? BTW, ANSI_PADDING is turned off.
> >
> > Thanks,
> > Gracie|||"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:DC0DFC0D-1017-40CD-A5C3-C6BD70224E62@.microsoft.com...
>I have a situation where the primary key is:
> user_id char(20) not null
> user_cd char(2) not null
> allows users to store trailing spaces, which in turn makes the key not
> unique.
> For example it allows the following, one with spaces one without:
> ernie 01
> ernie 01
>
I don't understand.
For char(20)
'ernie'
and
'ernie '
are stored the same, as
'ernie '
and are considered duplicates.
ANSI_PADDING has no effect on non-nullable char columns. ANSI_PADDING
controls whether trailing blanks are trimmed from varchar columns, and
whether nullable char columns are padded to width before being stored (this
is because nullable char columns are actually stored as as varchars).
But in general, I agree with what others have said: don't use for user_id,
use varchar. CHAR is ok for fixed-width coded like user_cd, but for data
which actually varies in length, varchar is better.
David|||Gracie,
I can't reproduce the behavior you describe. Regardless of the data types
or collations on the columns, ('ernie','01') and ('ernie ','01') will
be considered
duplicate keys, since ('ernie' = 'ernie ') and ('01' = '01') is true.
Trailing spaces
are ignored in string comparisons for all string data types and for all
collations,
as far as I know.
Steve Kass
Drew University
gracie wrote:
>I have a situation where the primary key is:
>user_id char(20) not null
>user_cd char(2) not null
>allows users to store trailing spaces, which in turn makes the key not unique.
>For example it allows the following, one with spaces one without:
>ernie 01
>ernie 01
>This in turn causes processing problems in the app.
>How do I fix this problem? BTW, ANSI_PADDING is turned off.
>Thanks,
>Gracie
>