Conditional Unique Constraint on Memory Optimized Tables

Question

I am trying to keep integrity in a MEMORY OPTIMIZED table I have. In that table is a foreign key (uniqueidentifier) that points to another table and an Active flag (bit) denoting whether the record is active or not.

I want to stop inserts from happening if the incoming record has the same foreign key as an existing record, only if the existing record is active (Active = 1).

Because this is a memory optimized table, I am limited in how I can go about this. I have tried creating a unique index and discovered they are not allowed in memory optimized tables.

UPDATE: I ended up using a stored procedure to solve my problem. The stored procedure will do the check for me prior to the insert or update of a record.

Answer 1

Most folks get around the limitations of In-Memory table constraints using triggers. There are a number of examples listed here:

https://www.mssqltips.com/sqlservertip/3080/workaround-for-lack-of-support-for-constraints-on-sql-server-memoryoptimized-tables/

Specifically for your case this will mimic a unique constraint for insert statements but the poster has examples for update and delete triggers as well in the link above.

-- note the use of checksum to make a single unique value from the combination of two columns

CREATE TRIGGER InMemory.TR_Customers_Insert ON InMemory.Customers
 WITH EXECUTE AS 'InMemoryUsr'
  INSTEAD OF INSERT  
AS
 SET NOCOUNT ON
 --CONSTRAINT U_OnDisk_Customersg_1 UNIQUE NONCLUSTERED (CustomerName, CustomerAddress)
  IF EXISTS (
    -- Check if rows to be inserted are consistent with CHECK constraint by themselves
    SELECT 0
        FROM  INSERTED I
    GROUP BY CHECKSUM(I.CustomerName, I.CustomerAddress) 
    HAVING COUNT(0) > 1
    UNION ALL

       -- Check if rows to be inserted are consistent with UNIQUE constraint with existing data
    SELECT 0
        FROM  INSERTED I
    INNER JOIN InMemory.tblCustomers C WITH (SNAPSHOT)
      ON  C.ChkSum = CHECKSUM(I.CustomerName, I.CustomerAddress)
    ) 
    BEGIN
      ;THROW 50001, 'Violation of UNIQUE Constraint! (CustomerName, CustomerAddress)', 1
    END
  INSERT INTO InMemory.tblCustomers WITH (SNAPSHOT)
      ( CustomerID ,
       CustomerName ,
       CustomerAddress,
    chksum
      )
  SELECT  NEXT VALUE FOR InMemory.SO_Customers_CustomerID ,
      CustomerName ,
      CustomerAddress,
   CHECKSUM(CustomerName, CustomerAddress)
 FROM INSERTED
 GO