2

I'm working on converting a SQL Anywhere (ASA) 5.5 based application and its database to MSSQL. Existing is a table with a primary key clustered on two columns, code_type char(4) and code char(30). I was able to build the table fine, but attempting to move over the data I ran into a conflict because the table contained ('cha', ' ') and ('cha', '') and speaking with another developer it sounds like this has a use case. Not really looking to retrain the users, what options are there to enforce a distinct number of spaces at the database level?

The database is for a single application so in the end I can rely on control of how data is inserted into the table but would like to enforce an intended distinction in the table definition.

  • What constraint are you trying to enforce? Could you add an example? – McNets Jan 9 at 19:41
  • 1
    Trailing spaces are ignored in equality operations in SQL Server so would the use case work? – scsimon Jan 9 at 20:31
  • @McNets If ('cha', '') exsists in the table, I'd like to be able to insert ('cha', ' ') as it has more trailing spaces, but not ('cha', '') because it has an equal number of spaces (0) as an existing entry. – Lawrence Anderson Jan 9 at 20:52
  • Why not a UNIQUE INDEX? – McNets Jan 9 at 22:05
  • 1
    This question on UNIQUE Index outlines the behavior on unique index, sql is applying right padding of spaces to make two strings equally sized before making the comparison per ANSI/ISO SQL-92 Section Section 8.2 General Rule #3. I've confirmed this behavior with the char type as the link used varchar. I'm not committed to using a key, just some way to enforce a distinct number of trailing spaces. – Lawrence Anderson Jan 9 at 22:32
0

I wouldn't call this a good solution but it seems to work. You can create a computed column with an appended character and create a unique index on code_type and the computed column. For the appended character you can use a character which will never appear in your data or any string of at least 31 character length if you'd like to avoid making assumptions like that. It's probably easiest to go through an example.

This is the problem that you're having:

CREATE TABLE dbo.T226714 (
    code_type CHAR(4) NOT NULL,
    code VARCHAR(30) NOT NULL,
    PRIMARY KEY (code_type, code)
);

-- fails due to PK error
INSERT INTO dbo.T226714 VALUES ('cha', ' ');
INSERT INTO dbo.T226714 VALUES ('cha', '');

Instead, do something like this:

DROP TABLE dbo.T226714;

CREATE TABLE dbo.T226714 (
    code_type CHAR(4) NOT NULL,
    code VARCHAR(30) NOT NULL,
    code_with_end_char AS CAST(code + 'ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ' AS VARCHAR(61))
);

CREATE UNIQUE INDEX UI ON dbo.T226714 (code_type, code_with_end_char);

-- works
INSERT INTO dbo.T226714 VALUES ('cha', ' ');
INSERT INTO dbo.T226714 VALUES ('cha', '');

-- can't insert this as a third row due to the unique index:
INSERT INTO dbo.T226714 VALUES ('cha', ' ');
  • That works for me. I will note that for the sake of thoroughness having [code] as data type varchar rather than char is required for this solution. With all columns as char duplicate key issue error (cha , ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ) – Lawrence Anderson Jan 10 at 16:43

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.