SQL Server中怎么實(shí)現(xiàn)約束增強(qiáng)，相信很多沒(méi)有經(jīng)驗(yàn)的人對(duì)此束手無(wú)策，為此本文總結(jié)了問(wèn)題出現(xiàn)的原因和解決方法，通過(guò)這篇文章希望你能解決這個(gè)問(wèn)題。

創(chuàng)新互聯(lián)從2013年創(chuàng)立，先為寧洱等服務(wù)建站，寧洱等地企業(yè)，進(jìn)行企業(yè)商務(wù)咨詢服務(wù)。為寧洱企業(yè)網(wǎng)站制作PC+手機(jī)+微官網(wǎng)三網(wǎng)同步一站式服務(wù)解決您的所有建站問(wèn)題。

在許多情況下，對(duì)外鍵使用更復(fù)雜的邏輯表達(dá)式是非常有用的。 此外，在某些情況下能夠在索引視圖創(chuàng)建約束也將非常實(shí)用。 我將舉例說(shuō)明，同時(shí)我希望針對(duì)此文的投票鏈接會(huì)盡快加上。當(dāng)外鍵中需要更為復(fù)雜的邏輯表達(dá)式時(shí) 考慮下面的簡(jiǎn)單常識(shí)： 您的設(shè)備的最大電流不能超過(guò)您插入到它的電路的最大電流。 假設(shè)下面的表存儲(chǔ)電路和設(shè)備數(shù)據(jù)：復(fù)制代碼 代碼如下: 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: 一個(gè)非常簡(jiǎn)便的命令就可能實(shí)現(xiàn)這個(gè)業(yè)務(wù)規(guī)則： 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: 然而，該語(yǔ)句并不被支持，所以必須采用其他辦法——多增加一列約束，使用3個(gè)而不是1個(gè)約束，如下所示： 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: 你可以驗(yàn)證該約束有效： 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 語(yǔ)句和CHECK約束"CHK_Devices_SufficientCurcuitMaximumCurrent"發(fā)生沖突。 該沖突發(fā)生在數(shù)據(jù)庫(kù)"Test"的"data.Devices"表。 該語(yǔ)句被終止執(zhí)行。 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. 可以看出，一個(gè)非常簡(jiǎn)單而普通的業(yè)務(wù)規(guī)則實(shí)現(xiàn)起來(lái)也相當(dāng)繁雜，因?yàn)閿?shù)據(jù)庫(kù)引擎并不直接支持這種業(yè)務(wù)規(guī)則。 When you want to create constraints on indexed views 在索引視圖上創(chuàng)建約束 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: 盡管數(shù)據(jù)庫(kù)保證“您的設(shè)備的最大電流不能超過(guò)您插入到它的電路的最大電流”，但這還不夠。請(qǐng)看下列示例數(shù)據(jù)： 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: 數(shù)據(jù)庫(kù)中的數(shù)據(jù)表明可以插入一個(gè)以上的設(shè)備到電路，這沒(méi)有錯(cuò)，可是當(dāng)所有的設(shè)備都打開(kāi)時(shí)，它們的最大電流之和會(huì)超過(guò)電路最大電流。為了加強(qiáng)這個(gè)業(yè)務(wù)規(guī)則，很自然的會(huì)創(chuàng)建一個(gè)索引視圖以使數(shù)據(jù)庫(kù)保證電流之和總是正確的。 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: 如果能在該索引視圖上創(chuàng)建一個(gè)約束，我將進(jìn)行這樣的設(shè)置： 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. 實(shí)際上，我必須使用觸發(fā)器或者精心拼湊Check約束來(lái)實(shí)現(xiàn)。如果數(shù)據(jù)庫(kù)內(nèi)置支持這種相當(dāng)普遍的業(yè)務(wù)規(guī)則，那將會(huì)增加SQL Server的實(shí)用性 。

看完上述內(nèi)容，你們掌握SQL Server中怎么實(shí)現(xiàn)約束增強(qiáng)的方法了嗎？如果還想學(xué)到更多技能或想了解更多相關(guān)內(nèi)容，歡迎關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道，感謝各位的閱讀！