CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008-r2
Gavin Mannion
Gavin Mannion

Reputation: 905

How to ensure my SQL varchar field is unique

I'm sure this is really simple but I've been up through the night and am now getting stuck.

I have a piece of functionality that clones a record in a database however I need to ensure the new name field is unique in the database.

eg, the first record is 

[ProjectName]   [ResourceCount]
'My Project'         8

Then when I click the clone I want

 'My Project Cloned', 8

But then if I hit the button again it should notice that the cloned name exists and rather spit out

'My Project Cloned 2', 8

Is that making sense?

I can do it with temp tables and cursors but there has to be a much nicer way to do this?

Using SQL Server 2008 R2

The solution needs to be entirely T-SQL based though, this occurs in a single stored procedure

Upvotes: 2

Views: 732

Answers (3)

steoleary
steoleary

Reputation: 9278

So from my understanding of your problem, here's how I would approach it:

My table:

CREATE TABLE [dbo].[deal]
(
[dealName] varchar(100),
[resourceCount] int
)

Then create a unique index on the dealName column:

CREATE UNIQUE NONCLUSTERED INDEX [UQ_DealName] ON [dbo].[deal]
(
[dealName] ASC
)

Once you have the unique index, you can then just handle any exceptions such as a unique constraint violation (error 2601) directly in T-SQL using try/catch

SET NOCOUNT ON;

DECLARE @dealName VARCHAR(100) = 'deal'
DECLARE @resourceCount INT = 8
DECLARE @count INT

BEGIN TRY
    BEGIN TRANSACTION
    INSERT INTO dbo.deal (dealName,resourceCount)
    VALUES (@dealName, @resourceCount)
    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    IF @@ERROR = 2601
    BEGIN
        ROLLBACK TRANSACTION
        SET @count = (SELECT COUNT(dealName) FROM dbo.deal WHERE resourceCount = @resourceCount)
        SET @resourceCount = (SELECT resourceCount FROM dbo.deal WHERE dealName = @dealName)
        SET @dealName = @dealName + ' Cloned ' + CAST(@count AS VARCHAR(100))
        BEGIN TRANSACTION
            INSERT INTO dbo.deal (dealName,resourceCount)
            VALUES (@dealName,@resourceCount)
        COMMIT TRANSACTION
    END
END CATCH
SELECT * FROM dbo.deal

You can easily put this code into a procedure, all it does is try and insert a deal name with the resource count, if the unique constraint is violated, it enters the catch block, appends the information that you want onto the deal name after finding the resource count of the original deal and then inserts these values.

It's not bulletproof by any means, but I find this technique really useful, not just for enforcing uniqueness, but you can use a similar way of handling exception numbers to deal with deadlocking, primary key violations and loads of other errors, all in T-SQL.

Upvotes: 3

Gavin Mannion
Gavin Mannion

Reputation: 905

I resolve this using an IF EXISTS inside a WHILE loop..

Personally I can't see what's wrong with this method but will obviously take any comments into account

DECLARE @NameInvalid varchar(100)
DECLARE @DealName varchar(100)
DECLARE @Count int
SET @Count = 1
SET @NameInvalid = 'true'

SELECT @DealName = DealName FROM Deal WHERE DealId = @DealId

--Ensure we get a unique deal name
WHILE( @NameInvalid = 'true')
BEGIN
    IF NOT EXISTS(SELECT DealName FROM Deal where DealName = @DealName + ' Cloned ' + cast(@Count as varchar(10)))
    BEGIN
        INSERT INTO Deal
            (DealName)
            SELECT @DealName + ' Cloned ' + cast(@Count as varchar(10))
                FROM Deal
                WHERE DealID = @DealId
            SET @NewDealId = @@IDENTITY
        SET @NameInvalid = 'false'
    END
    ELSE
    BEGIN
        SET @NameInvalid = 'true'
        SET @Count = @Count + 1
    END
END

Upvotes: 0

nathan_jr
nathan_jr

Reputation: 9282

Ensuring the value is unique is easy: Create a unique constraint. If a unique value is inserted MSSQL will throw an exception and you can recover in your application.

Creating a unique name based on a counter (Proj1, Proj2, etc.) is a bit more involved.

Note, this is best mitigated in the web layer, where you can perform an existence check and inform the user prior to attempting the insert that the project name "is already in use." And, if this isnt an option, there are far simpler methods of ensuring uniqueness than enumerating a count as you've described. Appending a datetime or guid would make things relatively easy and would greatly (if not completely) avoid race conditions.

If you absolutely must implement in t-sql as requested then incorporating a counter column somewhere (ie, my "sequence" table below) should help minimize race conditions. I suspect even with the below example you might see some contention under high frequency calls.

--setup
/*
    --your existing table
    create table dbo.Project 
    (
        [ProjectName] varchar(100) primary key, 
        [ResourceCount] int
    );

    --a new table to transactionally constrain the increment
    create table dbo.ProjectNameSequence (ProjectName varchar(100) primary key, Seq int);

    --cleanup
    --drop table dbo.ProjectNameSequence
    --drop table dbo.Project

*/

declare @ProjectName varchar(100), @ResourceCount int;

set @ProjectName = 'Test Project XX';
set @ResourceCount = 9;


merge dbo.ProjectNameSequence [d]
using (values(@ProjectName)) [s] (ProjectName) on 
      d.ProjectName = s.ProjectName
when matched then update set Seq += 1
when not matched then insert values(@ProjectName, 1)
output  @ProjectName + case inserted.Seq when 1 then '' else cast(inserted.Seq as varchar) end, 
        @ResourceCount
into dbo.Project;  


select * from dbo.Project

Upvotes: 1

Related Questions