Einzigartige Einschränkung (gekoppelt mit einer anderen Spalte eines bestimmten Wertes)

Ich habe ein database-Design geerbt, das hat eine Menge von Soft-Delete-Flags.

In dem unten stehenden Code versuche ich herauszufinden, ob es eine Einschränkung gibt, die "Unique EmployeeNumber für einen NON-Soft-deleted Employee" zulässt.

In diesem Mock-Beispiel kann EmployeeNumber's wiederverwendet werden, aber nur 1 EmployeeNumber kann nicht-Soft-deleted zu einem timepunkt sein.

Ich habe versucht, Zwänge (kommentiert unten), aber wusste genau, sie würden nicht arbeiten.

Irgendwelche Ideen?

Im folgenden Beispiel würde nur der 'FFFFFFFF-FFFF-FFFF-FFFFFFFFFFFF' fehlschlagen.

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] 

Sie tun das mit einem gefilterten eindeutigen Index.

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