CODEWITHSUNDEEP
sql-serversql-server-2008-r2
granadaCoder
granadaCoder

Reputation: 27904

Unique Constraint (Coupled with Another Column of a Certain Value)

I've inherited a database design, that has alot of soft-delete flags.

In the code below, I'm trying to figure out if there is a constraint that will allow "Unique EmployeeNumber for one NON-soft-deleted Employee".

In this mock example, EmployeeNumber's can be reused, but only 1 EmployeeNumber can be non-soft-deleted at a time.

I tried to constraints (commented out below), but knew right off they wouldn't work.

Any ideas?

In the example below, only the 'FFFFFFFF-FFFF-FFFF-FFFF-FFFFFFFFFFFF' would fail.

IF EXISTS ( SELECT * FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = N'dbo' and TABLE_NAME = N'Employee' and TABLE_TYPE = N'BASE TABLE' ) 
BEGIN 
DROP TABLE [dbo].[Employee] 
END 
GO



CREATE TABLE [dbo].[Employee] ( 

    [EmployeeUUID] [uniqueidentifier] NOT NULL,
    [EmployeeNumber] [varchar](10) NOT NULL,
    [LastName] [varchar](64) NOT NULL,
    [FirstName] [varchar](64) NOT NULL,
    [CreateDate] [datetime] NOT NULL DEFAULT CURRENT_TIMESTAMP,
    [HireDate] [datetime] NOT NULL, 
    IsSoftDeleted bit not null default 0
    )

GO

ALTER TABLE [dbo].[Employee] ADD CONSTRAINT PK_Employee PRIMARY KEY NONCLUSTERED (EmployeeUUID) 
GO

/* Does not work for fairly obvious reasons */
/*
ALTER TABLE [dbo].[Employee] ADD CONSTRAINT CK_EmployeeNumber_Unique UNIQUE (EmployeeNumber) 
*/
GO

/* Does not work because it only allows one IsSoftDeleted=1 rowo, not multiples */
/*
ALTER TABLE [dbo].[Employee] ADD CONSTRAINT CK_EmployeeNumber_Unique UNIQUE (EmployeeNumber, [IsSoftDeleted]) 
*/
GO



INSERT INTO [dbo].[Employee] ( [EmployeeUUID] , [EmployeeNumber] , [LastName] , [FirstName] , [HireDate] , [IsSoftDeleted] ) 
Select 'AAAAAAAA-AAAA-AAAA-AAAA-AAAAAAAAAAAA' , '1111111111' , 'Jones' , 'Mary' , '01/01/2001' , 0

INSERT INTO [dbo].[Employee] ( [EmployeeUUID] , [EmployeeNumber] , [LastName] , [FirstName] , [HireDate] , [IsSoftDeleted] ) 
Select 'BBBBBBBB-BBBB-BBBB-BBBB-BBBBBBBBBBBBBBBB' , '1111111111' , 'Smith' , 'John' , '02/02/2002' , 1

INSERT INTO [dbo].[Employee] ( [EmployeeUUID] , [EmployeeNumber] , [LastName] , [FirstName] , [HireDate] , [IsSoftDeleted] ) 
Select 'CCCCCCCC-CCCC-CCCC-CCCC-CCCCCCCCCCCC' , '1111111111' , 'Apple' , 'Andy' , '03/03/2003' , 1

INSERT INTO [dbo].[Employee] ( [EmployeeUUID] , [EmployeeNumber] , [LastName] , [FirstName] , [HireDate] , [IsSoftDeleted] ) 
Select 'DDDDDDDD-DDDD-DDDD-DDDD-DDDDDDDDDDDD' , '1111111111' , 'Banana' , 'Ben' , '03/03/2003' , 1


/* This would be the only row that would fail to insert, because of the duplicate ([EmployeeNumber]='1111111111' and [IsSoftDeleted]='1') */
INSERT INTO [dbo].[Employee] ( [EmployeeUUID] , [EmployeeNumber] , [LastName] , [FirstName] , [HireDate] , [IsSoftDeleted] ) 
Select 'FFFFFFFF-FFFF-FFFF-FFFF-FFFFFFFFFFFF' , '1111111111' , 'Banana' , 'Ben' , '03/03/2003' , 0



Select * from [dbo].[Employee]

Upvotes: 2

Views: 128

Answers (1)

Mikael Eriksson
Mikael Eriksson

Reputation: 139010

You do that with a filtered unique index.

create unique index UX_Employee_EmployeeNumber 
  on dbo.Employee(EmployeeNumber) 
  where IsSoftDeleted = 0

Upvotes: 4

Related Questions