使用案例語句檢查約束

如果列的輸入值大於 3，我想在列上添加一個檢查約束，而不是它應該保存為 1 否則輸入的值。對於那種情況，我使用下面的查詢，但在插入數據時會顯示一條錯誤消息。

create table tblTestCheckConstraint 
(
id int,
NewColumn int
)


alter table tblTestCheckConstraint
add constraint chk_tblTestCheckConstraint_NewColumn1 CHECK  
(
   CASE
       WHEN NewColumn >4 THEN 1
       ELSE NewColumn
   END = 1
)

插入語句：

insert into tblTestCheckConstraint values ( 1,5)
insert into tblTestCheckConstraint values ( 1,2)

錯誤資訊：

Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint "chk_tblTestCheckConstraint_NewColumn1". The conflict occurred in database "DBName"
, table "dbo.tblTestCheckConstraint", column 'NewColumn'.
The statement has been terminated.

檢查約束只能用於驗證數據是否屬於給定的一組參數。該CHECK(...)子句本質上被驗證為布爾條件；如果結果為1，則插入行，如果結果為0未插入行，則向客戶端返回錯誤。

為了測試這一點，我創建了您的表以及您提出的檢查約束，並插入了幾行測試數據：

CREATE TABLE dbo.tblTestCheckConstraint /* always specify the schema */
(
id INT,
NewColumn INT
);

ALTER TABLE tblTestCheckConstraint
ADD CONSTRAINT chk_tblTestCheckConstraint_NewColumn1 CHECK  
(
   CASE
       WHEN NewColumn >4 THEN 1
       ELSE NewColumn
   END = 1
);

INSERT INTO dbo.tblTestCheckConstraint (newcolumn) VALUES (1);
INSERT INTO dbo.tblTestCheckConstraint (newcolumn) VALUES (2);
INSERT INTO dbo.tblTestCheckConstraint (newcolumn) VALUES (3);
INSERT INTO dbo.tblTestCheckConstraint (newcolumn) VALUES (5);

SELECT *
FROM dbo.tblTestCheckConstraint;

這會導致幾個錯誤並允許插入兩行：

結果：

當嘗試插入2和3（或實際上除1or之外的任何內容>4）時，約束將返回 false，指示 SQL Server 不執行插入，並返回上面看到的錯誤消息。

以下是如何使用觸發器完成此操作的範例：

USE tempdb

CREATE TABLE dbo.tblTestCheckConstraint 
(
id INT CONSTRAINT PK_tblTestCheckConstring PRIMARY KEY CLUSTERED IDENTITY(1,1),
NewColumn INT NULL
);
GO
CREATE TRIGGER dbo.trg on dbo.tblTestCheckConstraint
INSTEAD OF INSERT
AS 
BEGIN
   INSERT INTO dbo.tblTestCheckConstraint (NewColumn)
   SELECT NewColumn
   FROM INSERTED i
   WHERE i.NewColumn <=3 OR i.NewColumn IS NULL;

   INSERT INTO dbo.tblTestCheckConstraint (NewColumn)
   SELECT 1
   FROM INSERTED i
   WHERE i.NewColumn >3;
END;
GO
INSERT INTO dbo.tblTestCheckConstraint (NewColumn)
VALUES (1);
INSERT INTO dbo.tblTestCheckConstraint (NewColumn)
VALUES (2);
INSERT INTO dbo.tblTestCheckConstraint (NewColumn)
VALUES (3);
INSERT INTO dbo.tblTestCheckConstraint (NewColumn)
VALUES (4);
SELECT *
FROM dbo.tblTestCheckConstraint;

確保在嘗試插入數據之前從表中刪除約束，因為它可能與觸發器程式碼衝突：

ALTER TABLE dbo.tblTestCheckConstraint
DROP CONSTRAINT chk_tblTestCheckConstraint_NewColumn1;

您真的不想要一個計算列（根據您的標題），這樣您就可以保留原始值併計算一個新值嗎？例如

CREATE TABLE dbo.tblTestCheckConstraint 
   (
   id INT PRIMARY KEY,
   NewColumn INT NOT NULL,
   ccNewColumn AS CASE WHEN NewColumn > 4 THEN 1 ELSE NewColumn END
   )

INSERT INTO dbo.tblTestCheckConstraint ( id, NewColumn )
VALUES ( 1, 1 ), ( 2, 2 ), ( 3, 3 ), ( 4, 4 ), ( 5, 5 )

引用自：https://dba.stackexchange.com/questions/75098