Question: SQL Server simple problem - ensure that only 1 batch is active at one time. Each batch has an assigned BatchID# - however that ID# might not be unique if a batch is re-run at some point. However, for a given ID# there should only ever be one "active" batch - that is only one batch with BatchComplete equal to NULL.

So the table I have is:

```
CREATE TABLE BatchTable (
     BatchNumber INT IDENTITY(1,1) PRIMARY KEY
     ,BatchID INT NOT NULL
     ,BatchStart DATETIME2(2) NOT NULL DEFAULT GETDATE()
     ,BatchComplete DATETIME2(2) NULL
     ,BatchLastOperation DATETIME2(2) DEFAULT GETDATE()
     BatchState INT NOT NULL
);

```

Then I have a constraint:

```
ALTER TABLE [dbo].[BatchTable]
ADD CONSTRAINT bt_OnlyOneBatchNumber UNIQUE (BatchID, BatchComplete)
GO

```

But last night this failed -- two inserts occurred in the same moment and both were inserted - chaos ensued. 

I am guessing the problem was that `("20160619", NULL) != ("20160619", NULL)` and therefore the rows were seen as unique. So the solution would be to create a non-null "IsComplete" column rather than relying upon the existence of the datetime in BatchComplete column. 

So my questions are:

1. Is my guess correct?
2. Is there a better way? Surely this kind of pattern has to be pretty common (I have done something similar before though I used triggers rather than a constraint).

The BatchState might work rather than IsCompete -- but the constraint would need to be for a given BatchID all *other* rows must have a BatchState greater or equal to 4. Not sure how to make that constraint.

Answer: 
I don't have access to SQL Server so I googled the syntax for generated columns, but there's probably syntax errors in here anyhow. The idea is to use a generated column that has a unique value (BatchNumber) whenever BatchComplete is not null, and a non-unique number (BatchId) otherwise:

```
CREATE TABLE BatchTable 
( BatchNumber INT IDENTITY(1,1) PRIMARY KEY 
, BatchID INT NOT NULL
, BatchComplete DATETIME2(2) NULL
, Active AS (CASE WHEN BatchComplete IS NULL 
                  THEN BatchID 
                  ELSE BatchNumber 
             END) PERSISTED
, CONSTRAINT ONLY_ONE_ACTIVE_AT_ATIME UNIQUE ( BatchID, Active )
);

```

If BatchComplete IS NULL then UNIQUE (BatchId, BatchId) must hold, i.e only one Active batch at a time.

If BatchComplete IS NOT NULL then UNIQUE (BatchId, BatchNumber) must hold, but BatchNumber itself is unique, so this will always be true.

As mentioned I can't test this, but you might be able to use the idea one way or another.
You can Use ajax request for checking and in codeigniter has built in function Is\_Unique[databse.table\_name]