SQL Server约束增强的两点建议

时间:2024-01-28 04:43:10 

在许多情况下,对外键使用更复杂的逻辑表达式是非常有用的。 此外,在某些情况下能够在索引视图创建约束也将非常实用。 我将举例说明,同时我希望针对此文的投票链接会尽快加上。
当外键中需要更为复杂的逻辑表达式时
考虑下面的简单常识: 您的设备的最大电流不能超过您插入到它的电路的最大电流。 假设下面的表存储电路和设备数据:


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的实用性 。

标签:SQL,Server约束
0
投稿

猜你喜欢

  • django传值给模板, 再用JS接收并进行操作的实例

    2021-08-23 08:03:46
  • SQL Server查看login所授予的具体权限问题

    2024-01-22 11:39:14
  • python批量更改目录名/文件名的方法

    2022-04-01 11:25:37
  • PHP中DirectIO操作文件扩展的用法大全

    2023-06-13 13:58:45
  • python实现决策树分类

    2021-04-04 13:35:28
  • Python实现字符串格式化输出的方法详解

    2023-05-02 20:52:59
  • 设计稿标注首屏线的确定始末

    2011-03-30 12:36:00
  • WEB界面设计五种特征

    2010-03-16 12:34:00
  • Python判断字符串是否包含特定子字符串的多种方法(7种方法)

    2021-09-20 02:22:51
  • Mysql auto_increment 重新计数(让id从1开始)

    2024-01-25 17:04:54
  • 一文带你探寻Python中的装饰器

    2021-07-11 10:17:59
  • Python利用Pillow处理图像的实践指南

    2023-05-07 09:15:30
  • 使用Javascript面向对象的思想编写ASP

    2008-06-16 12:20:00
  • MySQL安全性指南(3)(转)

    2024-01-21 23:46:07
  • 解决Pyinstaller打包软件失败的一个坑

    2022-05-31 21:47:14
  • pandas应用实例之pivot函数详解

    2021-02-16 18:33:18
  • javascript实现鼠标选取拖动或Ctrl选取拖动

    2021-08-21 19:08:33
  • ASP中Utf-8与Gb2312编码转换乱码问题的解决方法 页面编码声明

    2012-11-30 20:45:55
  • 在linux下实现 python 监控usb设备信号

    2022-04-18 15:29:24
  • Python使用BeautifulSoup库解析HTML基本使用教程

    2021-12-22 17:48:05
  • asp之家 网络编程 m.aspxhome.com