SQL Server约束增强的两点建议

2014-08-22  来源:本站原创  分类:MsSql  人气:2 

我们可以在CHECK的约束条件中加入任何逻辑表达式,而目前所有外键只能用来验证一个或多个列的值是否相等。

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

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约束增强的两点建议 2014-08-22

    我们可以在CHECK的约束条件中加入任何逻辑表达式,而目前所有外键只能用来验证一个或多个列的值是否相等. 在许多情况下,对外键使用更复杂的逻辑表达式是非常有用的. 此外,在某些情况下能够在索引视图创建约束也将非常实用. 我将举例说明,同时我希望针对此文的投票链接会尽快加上. 当外键中需要更为复杂的逻辑表达式时 考虑下面的简单常识: 您的设备的最大电流不能超过您插入到它的电路的最大电流. 假设下面的表存储电路和设备数据: CREATE TABLE Data.Curcuits(CurcuitID I

  • 2010年:给SQL Server DBA的三个建议 2014-07-15

    这些天里我真应该写一篇Blog,题目就叫"我在客户见面会上被问过的笨笨问题".当然,这很危险--某位客户也可以写篇类似的Blog,题目叫"我从Donald Farmer那里得到的愚蠢回答".不过,有一天某位CIO倒问了我一个非常好的问题: "我们一直在投资培训DBA所需的那些核心技能,但除此之外,如果我们有几个小时或一个礼拜的机会培训他们一些新技能,你建议应该培训哪些方面?"在随后与她的Email交流中,我给出了本文中余下部分的建议. 在新的一年

  • SQL Server 2008 R2数据库镜像部署图文教程 2014-12-21

    数据库镜像是一种针对数据库高可用性的基于软件的解决方案.其维护着一个数据库的两个相同的副本,这两个副本分别放置在不同的SQL Server数据库实例中 概述 "数据库镜像"是一种针对数据库高可用性的基于软件的解决方案.其维护着一个数据库的两个相同的副本,这两个副本分别放置在不同的SQL Server数据库实例中.建议使用不同位置的两台服务器来承载.在同一时刻,其中一台上的数据库用于客户端访问,充当"主体服务器"角色:而另一台则根据镜像会话的配置和状态,充当热备份服务

  • Sql Server 2012 转换函数的比较(Cast.Convert和Parse) 2015-01-12

    Cast.Convert 和 Parse 都是 Sql Server 2012 中的内置转换函数,其作用是:一种数据类型转换为另一种数据类型.其中前两者的差别较小,但 Parse 为 Sql Server 2012 新增函数,也是最近在分析 Sql Server 2012 新特性的时间,才有想把这3个函数拿出来进行比较分析的想法 语法结构: 1. Cast 语法结构:CAST ( expression AS data_type [ ( length ) ] ) 2. Convert 语法结构:C

  • SQL Server 2008成就"企业数据平台" 2014-07-24

    对于即将发布的SQL Server 2008,微软期望市场不仅仅是把它作为关系数据库系统来看待,微软将其定位为一个企业数据平台,虽然关系数据库引擎仍然是SQL Server 2008的核心,不过SQL Server 2008所能提供的服务广度将远超过简单的关系数据库存储系统.SQL Server 2008将继续延续SQL Server 7.0所设定的技术路线,提供端对端的信息平台. SQL Server 7.0引入的联机分析处理服务和商业智能技术标志着SQL Server从一个标准关系数据库服务

  • windows 7 下装visual studio 2008与sql server 2008的一些经验 2010-04-02

    经过了几次错误的安装,重新安装操作系统后,vs2008 与sql server 2008总算是安装成功了,现在我把这些经验给大分享一下. 安装操作系统(windows 7 旗舰版)后,最好是纯净的安装版,接着安装虚拟光驱,像那个poweriso ,daemon tools 等,一般情况下都会要重启.现在就可以安装vs2008了. 如果你的内存够大(2G以上)CPU主频够高(2.6GHz以上)的话,安装其可以做点别的,如果很小的话,最好不要动电脑,让电脑自行运行,否则电脑可能会卡死或者慢死!!!

  • SQL Server 2012大幅增强了T-SQL 2015-02-24

    SQL Server 2012对T-SQL进行了大幅增强,其中包括支持ANSI FIRST_VALUE和LAST_VALUE函数,支持使用FETCH与OFFSET进行声明式数据分页,以及支持.NET中的解析与格式化函数. Fetch与Offset 目前,对于实现服务端分页,SQL Server开发人员倾向于选择使用命令式技术,如将结果集加载入临时表,对行进行编号,然后从中挑选感兴趣的范围.有一些开发人员选择使用更加时髦的ROW_NUMBER和OVER模式.另外,还有一些开发人员坚持使用游标.虽然

  • 对于SQL Server数据迁移至PostgreSQL出错的解释以及解决建议 2014-09-09

    最近对SQL Server到PostgreSQL的数据迁移时出现了问题,返回的错误为:invalid byte sequence for encoding "UTF8": 0x00. 经查证pg源代码,该问题引起的原因是sql server的字符类型字段中含有空字符,该字符在pg中不支持. 问题重现: 1.PG客户端: postgres=# create table text_test (id int,info text); CREATE TABLE postgres=# insert

  • SQL SERVER的优化建议与方法 2015-01-23

    在实际的工作中,尤其是在生产环境里边,SQL语句的优化问题十分的重要,它对数据库的性能的提升也起着显著的作用.我们总是在抱怨机器的性能问题,总是在抱怨并发访问所带来的琐问题,但是如果我们对没一条SQL语句进行优化,尽管不能说可以解决全部问题,但是至少可以解决大部分问题. 1.Top排序问题. 我们经常要对表某个字段进行排序,然后取前N名.所以我们会写如下的SQL语句: select top 100 * from 表 order by Score desc 如果表非常大的话,那么这样的操作是非常消

  • DBA必读:SQL Server 2008安全概述 2015-03-19

    1.导言 随着网络越来越多的连接到一起,安全就变得更为重要.必须保护你的公司的设备,特别是它的数据库,它包含了你公司的非常重要的信息.安全是一个数据库引擎的重要特性之一,它保护企业免受众多威胁.Microsoft® SQL Server™ 2008的安全特性是设计用来使它更加安全并使得对于那些有责任保护数据安全的人们来说安全是触手可及和容易理解的. 在过去的几年,人们已经对什么是基于计算机的系统的安全开发了一套非常成熟的理念.Microsoft 站在这个开发的最前沿,而SQL Server 是第

  • SQL Server 致程序员(容易忽略的错误) 2013-12-16

    标签:SQL SERVER/MSSQL/DBA/T-SQL好习惯/数据库/需要注意的地方/程序员/容易犯的错误/遇到的问题 概述 因为每天需要审核程序员发布的SQL语句,所以收集了一些程序员的一些常见问题,还有一些平时收集的其它一些问题,这也是很多人容易忽视的问题,在以后收集到的问题会补充在文章末尾,欢迎关注,由于收集的问题很多是针对于生产数据,测试且数据量比较大,这里就不把数据共享出来了,大家理解意思就行. 步骤 大小写 大写T-SQL语言的所有关键字都使用大写,规范要求. 使用";"

  • SQL Server常用管理命令小结 2014-01-05

    需要管理sql server的朋友,需要掌握的,大家可以收藏下,方便以后使用 1. 查看数据库的版本 select @@version 2. 查看数据库所在机器操作系统参数 exec master..xp_msver 3. 查看数据库启动的参数 sp_configure 4. 查看数据库启动时间 select convert(varchar(30),login_time,120) from master..sysprocesses where spid=1 查看数据库服务器名和实例名 print

  • SQL Server触发器及触发器中的事务学习 2014-03-06

    首先, 说下我写篇文章的目的,我希望能把我对触发器的理解,分享出来与你一起学习 如果你有对触发器和事务的概念,有些了解,这篇文章,对你来说会是很简单,或能让你更进一步的了解触发器里面的一些故事,和触发器中事务个故事.在这边文章里面,我不会从触发器和事务的概念去讲述,而是从常见的两种触发器类型(DML触发器 & DDL触发器)和After触发器 & Instead Of 触发器的应用不同,开始说起它们,然后是说与事务有关的故事.如果,你有什么建议和意见,都可以通过文章后面的回复与我沟通,或者

  • Oracle8i和Microsoft SQL Server比较 2014-04-11

    正在看的ORACLE教程是:Oracle8i和Microsoft SQL Server比较. 体系结构 Microsoft按照客户/服务器体系结构的分布进行操作.这种方法产生不必要的代价和复杂性.在Internet中,Oracle已经发现了一个较好的答案.在Internet Computing的多层(multi-tiered)体系结构中,集中(centralization)可以简化应用的部署和维护,数据的管理和备份,并向客户提供了高级的性能.安全性与可靠性--结果使总的操作成本更低.Oracle

  • 基于Win2008 R2的WSFC实现 SQL Server 2012高可用性组(AlwaysOn Group) 2014-07-04

    SQL Server 2012高可用性组在实现过程中较之早起的SQL Server故障转移群集来讲,不依赖特别提供共享存储磁盘阵列,每个节点独立存储一份数据库的副本 两年前的<SQL Server 2008 R2数据库镜像部署>,今天"再续前缘"-- 微软新一代数据库产品SQL Server 2012已经面世一段时间了,不管从功能上讲还是性能上的体现,较之其早期产品都有了很大提升.特别是其引入高可用性组(AlwaysOn Group, AG)这一概念和功能,大大增强和提高了

  • 如何升级复制SQL Server 2008数据库 2014-08-10

    SQL Server 2008 支持从 SQL Server 的早期版本升级复制数据库:在升级某一节点时,不需要停止其他节点的活动.请务必遵守有关拓扑中支持哪些版本的规则: SQL Server 2000 Service Pack 3 (SP3) 是参与 SQL Server 2008 的复制拓扑必需的最低版本.如果使用的是 SQL Server 2005,则没有必需的最低版本. 分发服务器的版本可以是高于或等于发布服务器版本的任何版本(在许多情况下,分发服务器与发布服务器是同一个实例). 发布

  • SQL Server错误代码大全及解释(留着备用) 2014-09-10

    SQL Server错误代码大全及解释,以后遇到错误就可以根据对照表查看了 SQLSTATE SQL SERVER 驱动程序错误 描述 HY000 所有绑定列都是只读的. 必须是可升级的列,以使用 SQLSetPos 或 SQLBulkOperations 更改或插入行. HY000 已检测到一个旧 netlib (%s).请删除并重新启动应用程序. 正在装载的 netlib 已过期.驱动程序请求一个较新的 netlib. 问题可能出在应用程序当前目录中的 netlib,正在装载的是这个 net

  • SQL Server 数据库管理常用的SQL和T-SQL语句 2014-10-07

    1. 查看数据库的版本 select @@version 2. 查看数据库所在机器操作系统参数 exec master..xp_msver 3. 查看数据库启动的参数 sp_configure 4. 查看数据库启动时间 select convert(varchar(30),login_time,120) from master..sysprocesses where spid=1 查看数据库服务器名和实例名 print ''Server Name...............: '' + con

  • SQL Server 2005 数据库转 SQL Server 2000的方法小结 2014-11-03

    这篇文章主要介绍了SQL Server 2005 数据库转 SQL Server 2000的方法,需要的朋友可以参考下 今天有朋友推荐个活就是把Sql Server 2005的数据库转到Sql Server 2000里,但实际操作起来遇到的问题还不少,网上搜了一些办法,在此陈列出来,并把我使用时遇到的问题及解决方案一并记录下来,以备后期查阅 前几步和网上说的一样,先生成2000的脚本,然后再到2000里面执行.操作如下: 打开 SQL Server 2005 的Manger Studio --

  • ms SQL server数据库备份.压缩与SQL数据库数据处理的方法 2015-01-25

    一.备份数据库 1.打开SQL企业管理器,在控制台根目录中依次点开Microsoft SQL Server 2.SQL Server组-->双击打开你的服务器-->双击打开数据库目录 3.选择你的数据库名称(如论坛数据库Forum)-->然后点上面菜单中的工具-->选择备份数据库 4.备份选项选择完全备份,目的中的备份到如果原来有路径和名称则选中名称点删除,然后点添加,如果原来没有路径和名称则直接选择添加,接着指定路径和文件名,指定后点确定返回备份窗口,接着点确定进行备份 二.还原