Append an incremental integer to database table row while insert when it is violating unique constraint on View

Question

I have database table named Form with 2 columns Id and UserName.

I have created view on the table which is having columns Id and UserName.

On that view I have created UNIQUE CLUSTERED INDEX for column UserName So, that whenever user try to insert duplicate value for UserName it will throw unique constraint violation exception.

Is there any way to achieve below requirement,
I need to append an incremented integer to make the UserName unique.

When user enter UserName which is already present in table it should append incremented integer to it.

When inserting Username , we don't always just append a "1" to the end.
I need to append an incremented integer to make the UserName unique.

This matches the behavior of Windows when copying files.
If user inserts "Tom" 3 times, then it should create "Tom 1", "Tom 2", and "Tom 3".
If the user inserts "Tom" again, then it should create "Tom 4".
Again Think about this: 
- If user manually inserts "Tom" and "Tom 2" 
- The user then inserts "Tom" - it should create "Tom 1".
   The user then inserts "Tom" again - it should create "Tom 3" because "Tom 2" already exists.
Its similar windows file directory. When we copy/pasting text document in some folder it over and over.

I need this functionality to achieve unique name in CQRS with EventSourcing ,C# application where When user copies some entity eg. FormName it should just append incremental integer to FormName on basis of FormNames present in Db and can return from db So, that we can show new FormName on UI.

Is this something that can be done within a TRY...CATCH block? — Sam cd
Completely handled at Database end would be preferred, If it is not possible we can think about TRY...CATCH block... — user5347051

Sam cd Sam cd · Accepted Answer · 2015-09-17T16:39:19

You can try catching the specific error within a TRY...CATCH block in SQL, then editing the value you are inserting:

declare @insert bit = 1
declare @uName varchar(20) = 'Tom'
declare @iteration int = 1
WHILE @insert = 1
BEGIN
    BEGIN TRY
    BEGIN TRANSACTION
      INSERT INTO [table](UserName) VALUES
      (@uName)
      SET @insert = 0
    COMMIT TRANSACTION
    END TRY

    BEGIN CATCH
      IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION
      SET @insert = 0
      IF ERROR_NUMBER() = 2601
      BEGIN
        IF ISNUMERIC(RIGHT(@uName,1)) = 1
        BEGIN
          SET @iteration = convert(int,RIGHT(@uName,1)) + 1
          SET @uName = LEFT(@uName,LEN(@uName) - 1)
        END
        SET @uName = @uName + convert(varchar,@iteration)
        SET @insert = 1
      END
    END CATCH
END

To get the exact ERROR_NUMBER() you are looking for, you can do a test run, intentionally inserting a value that will violate the unique constraint, where your CATCH block is:

BEGIN CATCH
  SELECT ERROR_NUMBER()
END CATCH

EDIT As @Roshan pointed out, the correct ERROR_NUMBER() for this case is Error 2601

Append an incremental integer to database table row while insert when it is violating unique constraint on View

1 Answers