Уникальное ограничение (в сочетании с другим столбцом определенного значения)

Я унаследовал дизайн базы данных, в котором много флагов soft-delete.

В приведенном ниже коде я пытаюсь выяснить, существует ли ограничение, которое позволит «Уникальный номер EmployeeNumber для одного сотрудника, не прошедшего немедленный удаление».

В этом макетном примере EmployeeNumber может быть повторно использован, но только 1 EmployeeNumber может быть не-мягко удален одновременно.

Я попытался ограничить (прокомментировал ниже), но знал, что они не сработают.

Есть идеи?

В приведенном ниже примере только «FFFFFFFF-FFFF-FFFF-FFFF-FFFFFFFFFFFFFF» потерпит неудачу.

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] 

Вы делаете это с фильтрованным уникальным индексом.

 create unique index UX_Employee_EmployeeNumber on dbo.Employee(EmployeeNumber) where IsSoftDeleted = 0 
Давайте будем гением компьютера.