Verificação de migração e restrições Chave Estrangeira
Não há suporte para restrições de verificação e chave estrangeira no In-Memory OLTP no SQL Server 2014. Esses constructos geralmente são usados para impor a integridade de dados lógicos no esquema e podem ser importantes para manter a exatidão funcional dos aplicativos.
As verificações de integridade lógica em uma tabela, como marcar e restrições de chave estrangeira, exigem processamento adicional em transações e geralmente devem ser evitadas para aplicativos sensíveis ao desempenho. No entanto, se essas verificações forem cruciais para seu aplicativo, haverá duas soluções alternativas.
Verificando restrições após uma operação de inserção, atualização ou exclusão
Essa solução alternativa é otimista, com base na suposição de que a maioria das alterações não viola as restrições. Nessa solução alternativa, os dados são modificados primeiro antes que as restrições sejam avaliadas. Se uma restrição for violada, ela será detectada, mas a alteração não será revertida.
Essa solução alternativa tem a vantagem de ter impacto mínimo no desempenho porque a modificação de dados não é bloqueada por verificações de restrição. No entanto, se ocorrer uma alteração que viole uma ou mais restrições, o processo para reverter essa alteração poderá levar muito tempo.
Impor restrições antes de uma operação de inserção, atualização ou exclusão
Essa solução alternativa emula o comportamento de restrições de SQL Server. As restrições são verificadas antes da modificação de dados ocorrer e encerrarão a transação se um marcar falhar. Esse método incorre em uma penalidade de desempenho em modificações de dados, mas garante que os dados dentro de uma tabela sempre satisfaçam as restrições.
Use essa solução alternativa quando a integridade de dados lógicos for crucial para a correção e as modificações que violam uma restrição são prováveis. No entanto, para garantir a integridade, todas as modificações de dados devem ocorrer por meio de procedimentos armazenados que incluem essas imposição. As modificações por meio de consultas ad hoc e outros procedimentos armazenados não imporão essas restrições e, portanto, poderão violá-las sem aviso.
Exemplo de código
Os exemplos a seguir são baseados no banco de dados AdventureWorks2012. Especificamente, esses exemplos são baseados em [Vendas]. Tabela [SalesOrderDetail] e suas restrições de marcar e chave estrangeira associadas, além do índice exclusivo.
Os procedimentos armazenados especificados aqui são apenas para operações de inset. Os procedimentos armazenados para operações de atualização e exclusão devem ter estruturas semelhantes.
Definição de tabela para as soluções alternativas
Antes de converter em uma tabela com otimização de memória, a definição para [Sales]. [SalesOrderDetail] é o seguinte:
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
Depois de converter em uma tabela com otimização de memória, a definição para [Sales]. [SalesOrderDetail] é o seguinte:
Observe que rowguid não é mais um ROWGUIDCOL, pois não tem suporte no In-Memory OLTP. A coluna foi removida. Além disso, LineTotal é uma coluna computada e fora do escopo deste artigo, portanto, ela também foi removida.
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
Verificando restrições após uma operação de inserção, atualização ou exclusão
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
Impor restrições antes de uma operação de inserção, atualização ou exclusão
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