Export (0) Print
Expand All

Migrating Check and Foreign Key Constraints

SQL Server 2014

Check and foreign key constraints are not supported in In-Memory OLTP in SQL Server 2014. These constructs are usually used to enforce logical data integrity in the schema and can be important to maintaining the functional correctness of applications.

Logical integrity checks on a table such as check and foreign key constraints require additional processing on transactions and should generally be avoided for performance-sensitive applications. However, if such checks are crucial to your application, there exist two workarounds.

This workaround is optimistic, based on the assumption that the majority of changes do not violate the constraints. In this workaround, data is modified first before the constraints are evaluated. If a constraint is violated, it would be detected, but the change will not be rolled back.

This workaround has the advantage of having minimal impact on performance because data modification is not blocked by constraint checks. However, if a change that violates one or more constraints does occur, the process to roll back that change could take a long time.

This workaround emulates the behavior of SQL Server constraints. The constraints are checked before data modification occurs and will terminate the transaction if a check fails. This method incurs a performance penalty on data modifications, but ensures that data inside a table always satisfies the constraints.

Use this workaround when logical data integrity is crucial to correctness and modifications that violate a constraint are likely. However, to guarantee integrity, all data modifications must occur through stored procedures that include these enforcements. Modifications through ad-hoc queries and other stored procedures will not enforce these constraints and therefore may violate them with no warning.

The following samples are based on the AdventureWorks2012 database. Specifically, these samples are based on the [Sales].[SalesOrderDetail] table and its associated check and foreign key constraints in addition to the unique index.

The stored procedures specified here are for inset operations only. Stored procedures for update and delete operations should have similar structures.

Before converting to a memory-optimized table, the definition for [Sales].[SalesOrderDetail] is as follows:

USE [AdventureWorks2012]
GO

CREATE TABLE [Sales].[SalesOrderDetail](
   [SalesOrderID] [int] NOT NULL,
   [SalesOrderDetailID] [int] IDENTITY(1,1) NOT NULL,
   [CarrierTrackingNumber] [nvarchar](25) NULL,
   [OrderQty] [smallint] NOT NULL,
   [ProductID] [int] NOT NULL,
   [SpecialOfferID] [int] NOT NULL,
   [UnitPrice] [money] NOT NULL,
   [UnitPriceDiscount] [money] NOT NULL CONSTRAINT [DF_SalesOrderDetail_UnitPriceDiscount]  DEFAULT ((0.0)),
   [LineTotal]  AS (isnull(([UnitPrice]*((1.0)-[UnitPriceDiscount]))*[OrderQty],(0.0))),
   [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL CONSTRAINT [DF_SalesOrderDetail_rowguid]  DEFAULT (newid()),
   [ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_SalesOrderDetail_ModifiedDate]  DEFAULT (getdate()),
 CONSTRAINT [PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID] PRIMARY KEY CLUSTERED 
(
   [SalesOrderID] ASC,
   [SalesOrderDetailID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [Sales].[SalesOrderDetail]  WITH CHECK ADD  CONSTRAINT [FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID] FOREIGN KEY([SalesOrderID])
REFERENCES [Sales].[SalesOrderHeader] ([SalesOrderID])
ON DELETE CASCADE
GO

ALTER TABLE [Sales].[SalesOrderDetail] CHECK CONSTRAINT [FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID]
GO

ALTER TABLE [Sales].[SalesOrderDetail]  WITH CHECK ADD  CONSTRAINT [FK_SalesOrderDetail_SpecialOfferProduct_SpecialOfferIDProductID] FOREIGN KEY([SpecialOfferID], [ProductID])
REFERENCES [Sales].[SpecialOfferProduct] ([SpecialOfferID], [ProductID])
GO

ALTER TABLE [Sales].[SalesOrderDetail] CHECK CONSTRAINT [FK_SalesOrderDetail_SpecialOfferProduct_SpecialOfferIDProductID]
GO

ALTER TABLE [Sales].[SalesOrderDetail]  WITH CHECK ADD  CONSTRAINT [CK_SalesOrderDetail_OrderQty] CHECK  (([OrderQty]>(0)))
GO

ALTER TABLE [Sales].[SalesOrderDetail] CHECK CONSTRAINT [CK_SalesOrderDetail_OrderQty]
GO

ALTER TABLE [Sales].[SalesOrderDetail]  WITH CHECK ADD  CONSTRAINT [CK_SalesOrderDetail_UnitPrice] CHECK  (([UnitPrice]>=(0.00)))
GO

ALTER TABLE [Sales].[SalesOrderDetail] CHECK CONSTRAINT [CK_SalesOrderDetail_UnitPrice]
GO

ALTER TABLE [Sales].[SalesOrderDetail]  WITH CHECK ADD  CONSTRAINT [CK_SalesOrderDetail_UnitPriceDiscount] CHECK  (([UnitPriceDiscount]>=(0.00)))
GO

ALTER TABLE [Sales].[SalesOrderDetail] CHECK CONSTRAINT [CK_SalesOrderDetail_UnitPriceDiscount]
GO

After converting to a memory-optimized table, the definition for [Sales].[SalesOrderDetail] is as follows:

Note that rowguid is no longer a ROWGUIDCOL as it is not supported in In-Memory OLTP. The column has been removed. In addition, LineTotal is a computed column and out of scope for this article, so it also has been removed.

USE [AdventureWorks2012]
GO

CREATE TABLE [Sales].[SalesOrderDetail](
   [SalesOrderID] [int] NOT NULL,
   [SalesOrderDetailID] [int] IDENTITY(1,1) NOT NULL,
   [CarrierTrackingNumber] [nvarchar](25) NULL,
   [OrderQty] [smallint] NOT NULL,
   [ProductID] [int] NOT NULL,
   [SpecialOfferID] [int] NOT NULL,
   [UnitPrice] [money] NOT NULL,
   [UnitPriceDiscount] [money] NOT NULL CONSTRAINT [DF_SalesOrderDetail_UnitPriceDiscount]  DEFAULT ((0.0)),
   [ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_SalesOrderDetail_ModifiedDate]  DEFAULT (getdate()),
   CONSTRAINT [PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID] PRIMARY KEY NONCLUSTERED 
   (
      [SalesOrderID] ASC,
      [SalesOrderDetailID] ASC
   ),
   INDEX [AK_SalesOrderDetail_rowguid] NONCLUSTERED HASH ([rowguid]) WITH (BUCKET_COUNT = 1048576),
   INDEX [IX_SalesOrderDetail_ProductId] NONCLUSTERED ([ProductId] ASC)
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)

GO

USE AdventureWorks2012
GO

CREATE PROCEDURE Sales.usp_insert_SalesOrderDetails 
   @SalesOrderId int, @CarrierTrackingNumber nvarchar(25) = null, @OrderQty smallint, @ProductId int, @SpecialOfferID int, 
   @UnitPrice money, @UnitPriceDiscount money = 0.00, @ModifiedDate datetime = null
AS
BEGIN
BEGIN TRANSACTION 
   -- handle defaults for the insert.
   -- This is to make the insert logic less complex. Default constraints on the table should be in sync with this logic.
   -- Conversely, you can write an INSERT statement for each case where one or more values for the three columns with default constraints are not specified.
   IF @ModifiedDate = null SET @ModifiedDate = GETDATE()


   -- Insert the row.
   INSERT INTO Sales.SalesOrderDetail 
      (SalesOrderID, CarrierTrackingNumber, OrderQty, ProductID, SpecialOfferID, UnitPrice, UnitPriceDiscount, ModifiedDate)
   VALUES 
      (@SalesOrderId, @CarrierTrackingNumber, @OrderQty, @ProductID, @SpecialOfferID, @UnitPrice, @UnitPriceDiscount, , @ModifiedDate) 

   -- Now handle constraints
   DECLARE @violations TABLE 
   ( 
      ConstraintName sysname,
      ViolatedValue1 sql_variant,
      ViolatedValue2 sql_variant
   )

   -- FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID
   IF NOT EXISTS (SELECT soh.SalesOrderId AS [Exists] FROM Sales.SalesOrderHeader soh WHERE soh.SalesOrderID = @SalesOrderId) 
      INSERT INTO @violations (ConstraintName, ViolatedValue1, ViolatedValue2) 
      VALUES (N'FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID', @SalesOrderId, NULL)
   -- FK_SalesOrderDetail_SpecialOfferProduct_SpecialOfferIDProductID
   IF NOT EXISTS (SELECT sop.SpecialOfferID, sop.ProductID FROM [Sales].[SpecialOfferProduct] sop WHERE sop.SpecialOfferID = @SpecialOfferID AND sop.ProductID = @ProductId)
      INSERT INTO @violations (ConstraintName, ViolatedValue1, ViolatedValue2) 
      VALUES (N'FK_SalesOrderDetail_SpecialOfferProduct_SpecialOfferIDProductID', @SpecialOfferId, @ProductId)
   -- CK_SalesOrderDetail_OrderQty
   IF NOT @OrderQty > 0 
      INSERT INTO @violations (ConstraintName, ViolatedValue1, ViolatedValue2) 
      VALUES (N'CK_SalesOrderDetail_OrderQty', @OrderQty, NULL)
   -- CK_SalesOrderDetail_UnitPrice
   IF NOT @UnitPrice >= 0.00
      INSERT INTO @violations (ConstraintName, ViolatedValue1, ViolatedValue2) 
      VALUES (N'CK_SalesOrderDetail_UnitPrice', @UnitPrice, NULL)
   -- CK_SalesOrderDetail_UnitPriceDiscout
   IF NOT @UnitPriceDiscount >= 0.00
      INSERT INTO @violations (ConstraintName, ViolatedValue1, ViolatedValue2) 
      VALUES (N'CK_SalesOrderDetail_UnitPriceDiscount', @UnitPriceDiscount, NULL)

   -- Return a rowset containing violated constraints. On an item that doesn't violate anything, should return an empty rowset.
   SELECT ConstraintName, ViolatedValue1, ViolatedValue2 FROM @violations
   COMMIT TRANSACTION
END

USE AdventureWorks2012
GO

CREATE PROCEDURE Sales.usp_insert_SalesOrderDetails 
   @SalesOrderId int, @CarrierTrackingNumber nvarchar(25) = null, @OrderQty smallint, @ProductId int, @SpecialOfferID int, 
   @UnitPrice money, @UnitPriceDiscount money = 0.00, @ModifiedDate datetime = null
AS
BEGIN
BEGIN TRY
   BEGIN TRANSACTION
   -- Verify the constraints first.
   -- FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID
   IF NOT EXISTS (SELECT soh.SalesOrderId FROM Sales.SalesOrderHeader soh WHERE soh.SalesOrderID = @SalesOrderId) 
      THROW 50547, N'This SalesOrderId does not exist in SalesOrderHeader', 1
   -- FK_SalesOrderDetail_SpecialOfferProduct_SpecialOfferIDProductID
   IF NOT EXISTS (SELECT sop.SpecialOfferID, sop.ProductID FROM [Sales].[SpecialOfferProduct] sop WHERE sop.SpecialOfferID = @SpecialOfferID AND sop.ProductID = @ProductId)
      THROW 50547, N'This combination of SpecialOfferID and ProductID does not exist in SpecialOfferProduct', 1 
   -- CK_SalesOrderDetail_OrderQty
   IF NOT @OrderQty > 0 
      THROW 50547, N'OrderQty must be greater than zero.', 1
   -- CK_SalesOrderDetail_UnitPrice
   IF NOT @UnitPrice >= 0.00
      THROW 50547, N'UnitPrice cannot be negative.', 1
   -- CK_SalesOrderDetail_UnitPriceDiscout
   IF NOT @UnitPriceDiscount >= 0.00
      THROW 50547, N'UnitPriceDiscount cannot be negative', 1

   -- All verifications have now passed. Proceed to insert.

   -- handle defaults for the insert.
   -- This is to make the insert logic less complex. Default constraints on the table should be in sync with this logic.
   -- Conversely, you can write an INSERT statement for each case where one or more values for the three columns with default constraints are not specified.

   IF @ModifiedDate = null SET @ModifiedDate = GETDATE()

   -- Calculate computed columnn and store it.
   DECLARE @LineTotal numeric(38, 6)
   SET @LineTotal = (isnull((@UnitPrice * ((1.0) - @UnitPriceDiscount)) * @OrderQty, (0.0)))

   -- Insert the row.
   INSERT INTO Sales.SalesOrderDetail 
      (SalesOrderID, CarrierTrackingNumber, OrderQty, ProductID, SpecialOfferID, UnitPrice, UnitPriceDiscount, ModifiedDate)
   VALUES 
      (@SalesOrderId, @CarrierTrackingNumber, @OrderQty, @ProductID, @SpecialOfferID, @UnitPrice, @UnitPriceDiscount, @ModifiedDate)
   COMMIT TRANSACTION
END TRY
BEGIN CATCH
   IF @@TRANCOUNT > 0
      ROLLBACK TRANSACTION
      THROW;
END CATCH
END
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft