SQL Server約束增強兩點建議

在許多情況下，對外鍵使用更複雜的邏輯運算式是非常有用的。 此外，在某些情況下能夠在索引檢視表建立約束也將非常實用。 我將舉例說明，同時我希望針對此文的投票連結會儘快加上。
當外鍵中需要更為複雜的邏輯運算式時
考慮下面的簡單常識： 您的裝置的最大電流不能超過您插入到它的電路的最大電流。 假設下面的表格儲存體電路和裝置資料： 複製代碼 代碼如下:CREATE TABLE Data.Curcuits(CurcuitID INT NOT NULL
CONSTRAINT PK_Curcuits PRIMARY KEY,
MaximumCurrent INT NOT NULL,
Description VARCHAR(100) NOT NULL);
GO
INSERT INTO Data.Curcuits(CurcuitID,
MaximumCurrent,
Description)
SELECT 1, 25, 'Deck and Garage';
GO
CREATE TABLE Data.Devices(DeviceID INT NOT NULL
CONSTRAINT PK_Devices PRIMARY KEY,
CurcuitID INT NULL,
MaximumCurrent INT NOT NULL,
Description VARCHAR(100) NOT NULL,
CONSTRAINT FK_Devices_Curcuits FOREIGN KEY(CurcuitID)
REFERENCES Data.Curcuits(CurcuitID)
);
GO

It would be very convenient to issue a simple command and implement this business rule:
一個非常簡便的命令就可能實現這個商務規則：
ALTER TABLE Data.Devices ADD CONSTRAINT FK_Devices_Curcuits
FOREIGN KEY(CurcuitID, MaximumCurrent)
REFERENCES Data.Curcuits(CurcuitID, MaximumCurrent)
MATCH ON((Data.Devices.CurcuitID = Data.Curcuits.CurcuitID) AND
(Data.Devices.MaximumCurrent <= Data.Curcuits.MaximumCurrent));
However, it is not supported, so I need to use a workaround, one more column and three constraints instead of one, as follows:
然而，該語句並不被支援，所以必須採用其他辦法——多增加一列約束，使用3個而不是1個約束，如下所示：
ALTER TABLE Data.Curcuits
ADD CONSTRAINT UNQ_Curcuits UNIQUE(CurcuitID, MaximumCurrent);
GO
ALTER TABLE Data.Devices ADD CurcuitMaximumCurrent INT NULL;
GO
ALTER TABLE Data.Devices DROP CONSTRAINT FK_Devices_Curcuits;
GO
ALTER TABLE Data.Devices ADD CONSTRAINT FK_Devices_Curcuits
FOREIGN KEY(CurcuitID, CurcuitMaximumCurrent)
REFERENCES Data.Curcuits(CurcuitID, MaximumCurrent)
ON UPDATE CASCADE;
GO
ALTER TABLE Data.Devices
ADD CONSTRAINT CHK_Devices_SufficientCurcuitMaximumCurrent
CHECK(CurcuitMaximumCurrent >= MaximumCurrent);
GO
You can verify that the constraints work:
你可以驗證該約束有效：
INSERT INTO Data.Devices(DeviceID,
CurcuitID,
MaximumCurrent,
CurcuitMaximumCurrent,
Description)
SELECT 1, 1, 50, 25, 'Electric car charger'
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint "CHK_Devices_SufficientCurcuitMaximumCurrent". The conflict occurred in database "Test", table "data.Devices".
The statement has been terminated.
INSERT 語句和CHECK約束"CHK_Devices_SufficientCurcuitMaximumCurrent"發生衝突。 該衝突發生在資料庫"Test"的"data.Devices"表。
該語句被終止執行。
As you have seen, the implementation of a very simple and very common business rule is quite involved, because such business rules are not directly supported by the database engine.
可以看出，一個非常簡單而普通的商務規則實現起來也相當繁雜，因為資料庫引擎並不直接支援這種商務規則。
When you want to create constraints on indexed views
在索引檢視表上建立約束
Even when your database guarantees that “the maximum current of your device cannot exceed the maximum current of the circuit you plug it into”, it is not good enough. Consider the following sample data:
儘管資料庫保證“您的裝置的最大電流不能超過您插入到它的電路的最大電流”，但這還不夠。請看下列樣本資料：
INSERT INTO Data.Devices(DeviceID,
CurcuitID,
MaximumCurrent,
CurcuitMaximumCurrent,
Description)
SELECT 2, 1, 15, 25, 'ShopVac';
INSERT INTO Data.Devices(DeviceID,
CurcuitID,
MaximumCurrent,
CurcuitMaximumCurrent,
Description)
SELECT 3, 1, 15, 25, 'Miter Saw';
The database structure allows to plug more than one device into a circuit, which is correct, but if you turn both devices on, their combined maximum current exceeds the circuit's maximum current. To enforce this business rule, it would be natural to create an indexed view, so that the database guarantees that the totals are always correct:
資料庫中的資料表明可以插入一個以上的裝置到電路，這沒有錯，可是當所有的裝置都開啟時，它們的最大電流之和會超過電路最大電流。為了加強這個商務規則，很自然的會建立一個索引檢視表以使資料庫保證電流之和總是正確的。
CREATE VIEW Data.TotalMaximumCurrentPerCircuit WITH SCHEMABINDING
AS
SELECT d.CurcuitID,
c.MaximumCurrent AS CircuitMaximumCurrent,
SUM(d.MaximumCurrent) AS TotalMaximumCurrent,
COUNT_BIG(*) AS NumDevices
FROM Data.Devices d JOIN Data.Curcuits c ON d.CurcuitID = c.CurcuitID
GROUP BY d.CurcuitID, c.MaximumCurrent;
GO
CREATE UNIQUE CLUSTERED INDEX Data_TotalMaximumCurrentPerCircuit
ON Data.TotalMaximumCurrentPerCircuit(CurcuitID);
GO
If I could create a check constraint on that indexed view, I would be all set:
如果能在該索引檢視表上建立一個約束，我將進行這樣的設定：
ALTER VIEW Data.TotalMaximumCurrentPerCircuit
ADD CONSTRAINT CHK_TotalMaximumCurrentPerCircuit_ValidCurcuit
CHECK(TotalMaximumCurrent <= CircuitMaximumCurrent)
Instead, I need to use triggers or rather contrived kludges. A built in native support for such quite common business rules would increase the usefulness of SQL Server.
實際上，我必須使用觸發器或者精心拼湊Check約束來實現。如果資料庫內建支援這種相當普遍的商務規則，那將會增加SQL Server的實用性 。