CHECK 및 FOREIGN KEY 제약 조건 마이그레이션
2014년 SQL Server In-Memory OLTP에서는 검사 및 외래 키 제약 조건이 지원되지 않습니다. 이러한 제약 조건은 주로 스키마에서 논리 데이터 무결성을 강화하는 데 사용되고 애플리케이션의 기능 수정을 유지할 때 중요할 수 있습니다.
CHECK 및 FOREIGN KEY 제약 조건과 같은 테이블의 논리 무결성 검사를 수행하려면 트랜잭션에서 추가 처리가 필요하고 일반적으로 성능에 민감한 애플리케이션을 피해야 합니다. 그러나 이러한 검사가 애플리케이션에 필수적인 경우 두 가지 해결 방법이 있습니다.
Insert, Update 또는 Delete 작업 후 제약 조건 검사
이 해결 방법은 대부분의 변경 사항이 제약 조건을 위반하지 않는다는 가정 하에서 낙관적입니다. 이 해결 방법에서 제약 조건을 평가하기 전에 데이터를 먼저 수정합니다. 제약 조건을 위반하는 경우 감지되지만 변경 내용이 롤백되지 않습니다.
이 해결 방법은 데이터 수정이 제약 조건 검사로 인해 차단되지 않으므로 성능에 최소한의 영향을 미친다는 장점이 있습니다. 그러나 하나 이상의 제약 조건을 위반하는 변경 사항이 있는 경우 변경되는 롤백 프로세스에 시간이 오래 걸릴 수 있습니다.
Insert, Update 또는 Delete 작업 전 제약 조건 적용
이 해결 방법은 SQL Server 제약 조건의 동작을 에뮬레이트합니다. 제약 조건은 데이터 수정이 발생하기 전에 검사되고 검사에 실패하면 트랜잭션을 종료합니다. 이 방법은 데이터 수정 시 성능 저하가 발생하지만 테이블 내의 데이터가 항상 제약 조건을 충족합니다.
논리 데이터 무결성이 수정에 필수적이고 제약 조건을 위반하는 수정일 가능성이 높은 경우 이 해결 방법을 사용하십시오. 그러나 무결성을 보장하기 위해 모든 데이터 수정은 이러한 적용을 포함하는 저장된 프로시저를 통해 수행되어야 합니다. 임시 쿼리 및 다른 저장 프로시저를 통한 수정은 이러한 제약 조건이 적용되지 않으므로 경고 없이 제약 조건을 위반할 수 있습니다.
샘플 코드
다음 샘플은 AdventureWorks2012 데이터베이스를 기반으로 합니다. 특히 이러한 샘플은 고유 인덱스와 더불어 [Sales].[SalesOrderDetail] 테이블 및 해당 관련 CHECK 및 FOREIGN KEY 제약 조건을 기반으로 합니다.
여기에 지정된 저장 프로시저는 삽입 작업만을 위한 것입니다. 업데이트 및 삭제 작업에 대한 저장 프로시저에는 유사한 구조가 있습니다.
해결 방법에 대한 테이블 정의
메모리 최적화 테이블로 변환하기 전에 [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
Insert, Update 또는 Delete 작업 후 제약 조건 검사
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
Insert, Update 또는 Delete 작업 전 제약 조건 적용
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