Migrar restricciones CHECK y de clave externa
Las restricciones check y foreign key no se admiten en In-Memory OLTP en SQL Server 2014. Estas construcciones se suelen usar para aplicar la integridad lógica de los datos en el esquema y pueden ser importantes para mantener el correcto funcionamiento de las aplicaciones.
Las comprobaciones de integridad lógica de una tabla, como las restricciones CHECK y de clave externa, requieren un procesamiento adicional en las transacciones y se deben evitar normalmente para las aplicaciones sensibles al rendimiento. Sin embargo, si estas comprobaciones son cruciales para la aplicación, existen dos soluciones alternativas.
Comprobar las restricciones después de una operación de inserción, actualización o eliminación
Esta solución alternativa es optimista y se basa en el supuesto de que la mayoría de cambios no infringen las restricciones. En esta solución alternativa, se modifican los datos antes de que se evalúan las restricciones. Si se infringe una restricción, se detectaría pero el cambio no se revertirá.
Esta solución alternativa presenta la ventaja de que tiene un impacto mínimo sobre el rendimiento porque la modificación de los datos no está bloqueada por las comprobaciones de las restricciones. Sin embargo, si se realiza un cambio que infringe una o más restricciones, el proceso para revertir ese cambio puede tardar mucho tiempo.
Aplicar las restricciones antes de una operación de inserción, actualización o eliminación
Esta solución alternativa emula el comportamiento de las restricciones de SQL Server. Las restricciones se comprueban antes de modificar los datos y finalizarán la transacción si se produce un error en una comprobación. Este método incurre en una penalización de rendimiento en las modificaciones de datos, pero garantiza que los datos de una tabla satisfacen siempre las restricciones.
Use esta solución alternativa cuando la integridad lógica de los datos sea fundamental para la exactitud y sea probable que haya modificaciones que infrinjan una restricción. Sin embargo, para garantizar la integridad, todas las modificaciones de datos deben realizarse mediante procedimientos almacenados que incluyan estos cumplimientos. Las modificaciones realizadas con consultas ad hoc y otros procedimientos almacenados no aplicarán estas restricciones y, por tanto, pueden infringirlas sin mostrar ninguna advertencia.
Código de ejemplo
Los ejemplos siguientes se basan en la base de datos AdventureWorks2012. En concreto, estos ejemplos se basan en la tabla [Sales].[SalesOrderDetail] y sus restricciones CHECK y de clave externa asociadas además del índice único.
Los procedimientos almacenados especificados aquí son para operaciones de inserción únicamente. Los procedimientos almacenados para las operaciones de actualización y eliminación deben tener estructuras similares.
Definición de tabla para las soluciones alternativas
Antes de convertirse a una tabla optimizada para memoria, la definición de [Sales].[SalesOrderDetail] es la siguiente:
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
Después de convertirse a una tabla optimizada para memoria, la definición de [Sales].[SalesOrderDetail] es la siguiente:
Tenga en cuenta que rowguid ya no es rowGUIDCOL, ya que no se admite en In-Memory OLTP. Se ha quitado la columna. Además, LineTotal es una columna calculada y queda fuera del ámbito de este artículo, por lo que también se ha quitado.
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
Comprobar las restricciones después de una operación de inserción, actualización o eliminación
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
Aplicar las restricciones antes de una operación de inserción, actualización o eliminación
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