SQL Server 2014의 In-Memory OLTP에서는 Check 및 Foreign Key 제약 조건이 지원되지 않습니다. 이러한 구문은 일반적으로 스키마에서 논리 데이터 무결성을 적용하는 데 사용되며 애플리케이션의 기능적 정확성을 유지하는 데 중요할 수 있습니다.
검사 및 외래 키 제약 조건과 같은 테이블에 대한 논리적 무결성 검사는 트랜잭션에 대한 추가 처리가 필요하며 일반적으로 성능에 민감한 애플리케이션에 대해서는 피해야 합니다. 그러나 이러한 검사가 애플리케이션에 중요한 경우 두 가지 해결 방법이 있습니다.
삽입, 업데이트 또는 삭제 작업 후 제약 조건 확인
이 해결 방법은 대부분의 변경 내용이 제약 조건을 위반하지 않는다는 가정하에 낙관적입니다. 이 해결 방법에서는 제약 조건이 평가되기 전에 먼저 데이터가 수정됩니다. 제약 조건을 위반하면 검색되지만 변경 내용은 롤백되지 않습니다.
이 해결 방법은 제약 조건 검사에 의해 데이터 수정이 차단되지 않으므로 성능에 최소한의 영향을 미칠 수 있다는 장점이 있습니다. 그러나 하나 이상의 제약 조건을 위반하는 변경이 발생하는 경우 해당 변경 내용을 롤백하는 프로세스는 시간이 오래 걸릴 수 있습니다.
삽입, 업데이트 또는 삭제 작업 전에 제약 조건 적용
이 해결 방법은 SQL Server 제약 조건의 동작을 에뮬레이트합니다. 제약 조건은 데이터 수정이 발생하기 전에 검사되며 검사에 실패하면 트랜잭션이 종료됩니다. 이 메서드는 데이터 수정에 대한 성능 저하를 발생시키지만 테이블 내의 데이터가 항상 제약 조건을 충족하도록 합니다.
논리 데이터 무결성이 정확성에 중요하고 제약 조건을 위반하는 수정이 가능한 경우 이 해결 방법을 사용합니다. 그러나 무결성을 보장하려면 이러한 적용을 포함하는 저장 프로시저를 통해 모든 데이터 수정이 이루어져야 합니다. 임시 쿼리 및 기타 저장 프로시저를 통한 수정은 이러한 제약 조건을 적용하지 않으므로 경고 없이 위반할 수 있습니다.
예제 코드
다음 샘플은 AdventureWorks2012 데이터베이스를 기반으로 합니다. 이러한 샘플은 고유 인덱스를 포함하여 [Sales].[SalesOrderDetail] 테이블과 관련된 검사 및 외래 키 제약 조건을 기반으로 합니다.
여기에 지정된 저장 프로시저는 삽입 작업에만 사용됩니다. 업데이트 및 삭제 작업에 대한 저장 프로시저에는 유사한 구조가 있어야 합니다.
해결 방법을 위한 테이블 정의
메모리 최적화 테이블로 변환하기 전에 [Sales].[SalesOrderDetail]에 대한 정의는 다음과 같습니다.
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
메모리 최적화 테이블로 변환한 후 [Sales].[SalesOrderDetail]에 대한 정의는 다음과 같습니다.
rowguid는 In-Memory OLTP에서 지원되지 않으므로 더 이상 ROWGUIDCOL이 아닙니다. 열이 제거되었습니다. 또한 LineTotal은 이 문서의 범위를 벗어난 계산 열이므로 제거되었습니다.
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