SQL建立与使用默认值条件约束及规则.docx

《SQL建立与使用默认值条件约束及规则.docx》由会员分享，可在线阅读，更多相关《SQL建立与使用默认值条件约束及规则.docx（21页珍藏版）》请在三一办公上搜索。

1、SQL建立与使用默认值条件约束及规则SQL建立与使用默认值、条件约束及规则 1、默认值使用CREATE TABLE建立默认值USE MyDBCREATE TABLE MyTABLE(columnA char(15) NULL DEFAULT n/a,columnB int NULL DEFAULT 0)GO使用ALTER TABLE命令可以修改数据行中的默认值定义或新增一数据行。若要更改已经定义的默认值数据行，首先必须删除已经有的默认值，然后新增一个新的默认值如果用CREATE TABLE命令建立未命名的默认值，SQL Server将自动替默认值命名。要知道SQL Server为默认值分配了什

2、么名字，以便可以使用T-SQL删除它，您可执行sp_help程序如下：USE MyDBGOsp_help MyTableGO假设我们要把columnA的默认值从n/a改成not applicable。记住首先必须删除存在的默认值然后再新增一个新的。下述命令即可删除默认值：ALTER TABLE MyTableDROP CONSTRAINT DF_MyTable_columnA_2B3F6F97现在您可以使用下述命令新增一个默认值，这次由我们自己命名:ALTER TABLE MyTableADD CONSTRAINT DF_MyTable_columnA DEFAULT Not applicab

3、le FOR columnAGO当变更已存在的默认值时，所有现存的列将保持原始的默认值。只有新插入的列会使用新的默认值。用ALTER TABLE命令为已有的数据表新增完整的新数据行，如下所示：ALTER TABLE MyTableADD columnC tinyint NOT NULL DEFAULT 13GO用默认值而不是NULL插入已存在的列，则应该使用DEFAULT中的WITH VALUES选项，如下所示：ALTER TABLE MyTableADD columnC tinyint NULL DEFAULT 13 WITH VALUESGOWITH VALUES命令会强行使MyTable

4、中所有现存列的新数据行接受默认值13来代替原来的NULL值。CREATE DEFAULT和sp_bindefault如果要在不同的数据表中使用相同的默认值数据行，此方法则较为有效。使用CREATE DEFAULT的语法如下：CREATE DEFAULT default_name AS constant_expressionsp_bindefault的语法如下：sp_bindefault default_name table.column | user_defined_datatype, futureonly示例：USE MyDBGOCREATE DEFAULT DF_not_applicabl

5、e AS n/aGOsp_bindefault DF_not_applicable, MyTable.columnAGO如果没有指定futureonly，SQL Server将默认值系结到所有已经存在的和新建立的使用者自订类型的数据行上例如，让我们建立一个名称为area_code的使用者自订型别和名称为DF_area_code的默认值对象，其值为786；然后系结默认值到该使用者自订数据型别上。因为这是新的使用者自订数据型别，因此目前还没有数据行，也就不需要futureonly选项sp_addtype area_code, char(3), NOT NULLGOCREATE DEFAULT DF

6、_area_code AS 786GOsp_bindefault DF_area_code, area_code, futureonlyGO要检视 预设 对象的数据型别，可使用sp_help系统程序sp_unbindefault例如要解除MyTable中与columnA数据行系结的默认值：sp_unbindefault如下：sp_unbindefault MyTable.columnAGO从使用者自订的数据型别area_code中解除系结默认值：sp_unbindefault area_codeGO当执行以上程序，所有已经由使用者自订数据型别area_code的默认值属性将会同时移除。同样的，

7、只要在不删除预设对象的情形下，您可以随意地解除或系结某个数据行的预设。使用DROP DEFAULT陈述式，可以完全删除一个 预设 对象如下所示：DROP DEFAULT DF_area_codeGO一旦删除了预设对象，就无法再取回。如果要再次使用，必须使用CREATE DEFAULT重新建立对象。在 默认值 储存格中输入字符串，必须放在单引号中，否则储存时会显示SQL Server的错误讯息。2条件约束条件约束用于自动维护数据的完整性。举个例子，您可以将一个整数数据行条件约束在1到100的范围内，那么超出此范围的数值则无法被接受条件约束的五种类型为NOT NULL、UNIQUE、PRIMARY

8、 KEY、FOREIGN KEY和CHECK使用T-SQL建立和修改 条件约束NOT NULLNOT NULL条件约束相当简单UNIQUEUNIQUE条件约束用以确保一个或多个数据行中没有重复的数值要用T-SQL为数据表建立UNIQUE条件约束，须执行CREATE TABLE或ALTER TABLE命令例如CREATE TABLE customer(first_name char(20) NOT NULL,mid_init char(1) NULL,last_name char(20) NOT NULL,SSN char(11) NOT NULL UNIQUE CLUSTERED,cust_p

9、hone char(10) NULL)GO例子CREATE TABLE customer(first_name char(20) NOT NULL,mid_init char(1) NULL,last_name char(20) NOT NULL,SSN char(11) NOT NULL UNIQUE CLUSTERED,cust_phone char(10) NULL,CONSTRAINT UQ_full_name UNIQUE NONCLUSTERED (first_name, mid_init, last_name)GO新增数据行条件约束和数据表条件约束的两组命令：ALTER TABL

10、E customerADD CONSTRAINT UQ_ssn UNIQUE CLUSTERED(SSN)GOALTER TABLE customerADD CONSTRAINT UQ_full_name UNIQUE NONCLUSTERED (first_name, mid_init, last_name)GO要用T-SQL来修改数据行或数据表中已有的UNIQUE条件约束，必须先删除条件约束再重新建立主索引键CREATE TABLE customer(first_name char(20) NOT NULL,mid_init char(1) NULL,last_name char(20)

11、NOT NULL,SSN char(11) PRIMARY KEY,cust_phone char(10) NULL)GO另一种可行的方法是以增加CONSTRAINT关键词来命名。使用下面的命令将主索引键命名为PK_SSN：CREATE TABLE customer(first_name char(20) NOT NULL,mid_init char(1) NULL,last_name char(20) NOT NULL,SSN char(11) CONSTRAINT PK_SSN PRIMARY KEY,cust_phone char(10) NULL)GO您也可以在定义了所有数据表的数据行

12、后，再指定PRIMARY KEY条件约束。数据行名称必须在括号中，并在CONSTRAINT后指定，语法如下所示：CREATE TABLE customer(first_name char(20) NOT NULL,mid_init char(1) NULL,last_name char(20) NOT NULL,SSN char(11),cust_phone char(10) NULL,CONSTRAINT PK_SSN PRIMARY KEY (SSN)GO以下为替customer资料表新增PRIMARY KEYALTER TABLE customerADD CONSTRAINT PK_SS

13、N PRIMARY KEY CLUSTERED (SSN)GO要删除PRIMARY KEY条件约束，须使用ALTER TABLE命令和DROP CONSTRAINT陈述式ALTER TABLE customerDROP CONSTRAINT PK_SSNGO注意，只有在DROP CONSTRAINT的陈述式中，需要条件约束名称。要使用T-SQL命令修改数据表中现存的PRIMARY KEY条件约束，必须先使用ALTER TABLE.DROP CONSTRAINT删除现存条件约束和ALTER TABLE. ADD CONSTRAINT陈述式来新增条件约束以修改资料表。外部索引键首先建立一个数据表，

14、命名为items，其中的item_id数据行具主索引键，如下所示：CREATE TABLE items(item_name char(15) NOT NULL,item_id smallint NOT NULL IDENTITY(1,1),price smallmoney NULL,item_desc varchar(30) NOT NULL DEFAULT noneCONSTRAINT PK_item_id PRIMARY KEY (item_id)GO建立inventory的数据表，其中有称为FK_item_id的外部索引键，此外部索引键引用items数据表的item_id如下所示：CRE

15、ATE TABLE inventory(store_id tinyint NOT NULL,item_id smallint NOT NULL,item_quantity tinyint NOT NULL,CONSTRAINT FK_item_id FORGIEN KEY(item_id)REFERENCES items(item_id)GO下面先删除inventory数据表的旧条件约束，然后新增条件约束的命令：ALTER TABLE inventoryDROP CONSTRAINT FK_item_idGOALTER TABLE inventoryADD CONSTRAINT FK_item

16、_id FOREIGN KEY (item_id)REFERENCES items(item_id)GO当您在现存资料行中新增FOREIGN KEY条件约束，SQL Server会检查数据表中现存的数据列，以确保除NULL值外，外部索引键数据行的值符合参照资料表的PRIMARY KEY条件约束或UNIQUE条件约束。当建立FOREIGN KEY条件约束时，可以使用ALTER TABLE的WITH NOCHECK选项，那么SQL Server就不会去验证现有的值，如下所示：ALTER TABLE inventoryWITH NOCHECK ADD CONSTRAINT FK_item_idFOR

17、EIGN KEY (item_id)REFERENCES items(item_id)GOWITH NOCHECK选项可避免SQL检查数据表中现存列的值。如此，无论现有值为何，都可以新增条件约束到数据表上。新增条件约束后，外部索引键的完整性也会增强。_注意 使用WITH NOCHECK选项时要小心。因为当您预计更新现存数据，但是现存数据中却包含与条件约束冲突的值，您将无法更新现存数据。_您也可以控制是否启用FOREIGNKEY条件约束。NOCHECK关键词表示忽略条件约束；CHECK关键词表示条件约束生效。ALTER TABLE inventoryNOCHECK CONSTRAINT FK_i

18、tem_id - 使条件约束无效GO-在此插入您需要的数据列ALTER TABLE inventory CHECK CONSTRAINT FK_item_id - 重新回复条件约束GO_说明 尽量不要插入与FOREIGN KEY条件约束冲突的资料行。否则可能导致将来数据表的有冲突的数据列无法被更新。_CHECKCHECK条件约束用于限制数据行中值的允许范围。条件约束中指定的布尔搜寻条件传回的是TRUE时，在数据行中插入或修改的值才算有效。例如，如果我们要限制items数据表的price数据行允许值的可能范围在 $0.01到 $500.00之间，应该使用下面的陈述式：CREATE TABLE i

19、tems(item_name char(15) NOT NULL,item_id smallint NOT NULL IDENTITY(1,1),price smallmoney NULL,item_desc varchar(30) NOT NULL DEFAULT noneCONSTRAINT PK_item_id PRIMARY KEY (item_id),CONSTRAINT CK_price CHECK (price >= .01 ANDprice <= 500.00)GO注意，我们在price数据行中允许NULL，且在数据行中有CHECK条件约束。由于SQL Server可以

20、辨别NULL值和其它型别的值，所以尽管有CHECK条件约束，price数据行仍然可允许为NULL值。同时需要注意的是，我们将这个条件约束命名为CK_price。我们之前曾看到，将条件约束命名在稍后即可简单地用T-SQL来删除和重新建立条件约束。例如，把值的范围修改为从 $1.00到 $1000.00之间，可以使用下面的陈述式：ALTER TABLE itemsDROP CONSTRAINT CK_priceGOALTER TABLE itemsADD CONSTRAINT CK_price CHECK (price >= 1.00 ANDprice <= 1000.00)GO第二个AL

21、TER TABLE命令应该与第一次为现存的items数据表新增条件约束时所使用的命令相同。为现存资料表新增CHECK条件约束和新增FOREIGN KEY条件约束所遵守的规则是一样的。所有现存的列将会根据条件约束来检查，如果所有回传的值不是TRUE，则无法将条件约束将新增到数据表中，而且SQL Server将回传错误讯息：指出ALTER TABLE陈述式与CHECK条件约束冲突。如果一定要新增条件约束，使用WITH NOCHECK指定现存的数据列不生效，而将来插入和修改的资料列得以生效。_注意 不建议使用WITH NOCHECK，因为将来可能无法更新不符合条件约束的列。_下面是增加CK_pric

22、e条件约束时使用WITH NOCHECK的一个例子：ALTER TABLE itemsWITH NOCHECK ADD CONSTRAINT CK_priceCHECK (price >= 1.00 AND price <= 1000.00)GO和FOREIGN KEY条件约束一样，在ALTER TABLE中使用CHECK和NOCHECK关键词也可以控制CHECK条件约束是否生效。您可能想用这种方法插入一个超出指定范围但依然有效的价格。下面的例子先停用CK_price条件约束，然后再使其生效：ALTER TABLE items NOCHECK CONSTRAINT CK_price -

23、 使条件约束无效 GO- 在此插入资料列GOALTER TABLE items CHECK CONSTRAINT CK_price - 重新启用条件约束 GO_说明 只有CHECK和FOREIGN KEY类型的条件约束可以用这种方式来控制是否生效。_用Enterprise Manager建立和修改条件约束这部分将学习如何用Enterprise Manager设计数据表窗口来建立、修改和删除条件约束，以及在FOREIGN KEY条件约束的情况下，建立数据库图表。使用Enterprise Manager来建立新资料表或编辑现存数据表时，将显示设计数据表窗口。要建立新数据表，在Enterprise

24、Manager的左边窗格中展开服务器和数据库数据夹，在数据表上按右钮，从快捷菜单中选择 新增 / 数据表 。要显示现存数据表的 设计数据表 窗口，首先在左边窗格中找出展开 数据表 ，在右边窗格中的数据表名称上按右钮，然后从快捷菜单中选择 设计数据表 。允许NULL值要指定数据行中是否允许NULL值，在 设计数据表 窗口中的 是否允许NULL 标题下简单地选取或清除适当的复选框即可。您可以在建立数据表或修改数据表时设定这一选项。关于允许NULL值的规则请参见 第10章 。图16-11显示了本章前面 用T-SQL建立和修改数据表 我们曾经建立的customer数据表的 设计数据表 窗口。您可以看到

25、mid_init和cust_phone两个数据行允许NULL值，但其它三数据行不允许NULL值。图16-11 customer数据表设计数据表窗口的是否允许NULL数据行的设定UNIQUE1. 要使用Enterprise Manager来建立和修改条件约束，请遵循以下步骤： 在 设计数据表 窗口中，在工具列中选择 数据表索引属性 按钮，在 属性 对话框中选择 索引/索引键 卷标页。图16-12显示了customer数据表的 属性 对话框中的 索引/索引键 卷标页。使用下面的命令来建立这张数据表。数据表的SSN数据行包括一条作为丛集索引的UNIQUE条件约束：CREATE TABLE custo

26、mer(first_name char(20) NOT NULL,mid_init char(1) NULL,last_name char(20) NOT NULL,SSN char(11) NOT NULL UNIQUE CLUSTERED,cust_phone char(10) NULL)GO图16-12 customer数据表的属性对话框中的索引/索引键卷标页2. 要建立一个新的UNIQUE条件约束，在 属性 对话框的 索引/索引键 卷标页上按 新增 按钮，选择条件约束使用的数据行名称，键入新的条件约束名称，然后选择 建立成唯一 - UNIQUE 复选框。如果要使它作为数据行的丛集索引，

27、在数据表中选择 建立成丛集 - CLUSTERED 复选框，并指定填满因子。如果不要SQL Server定期地自动计算索引数据，则选取选取方块旁的选项。 3. 您可以使用 属性 对话框修改UNIQUE条件约束。例如，修改条件约束名称、指定要新增到条件约束的数据行、设定丛集索引选项，以及为索引选择填充因子等。对条件约束进行修改，完成后按一下 关闭 按钮，然后在Enterprise Manager中按 储存 按钮储存修改。 主索引键您可以为一或多个数据行指定一个主索引键条件约束。按以下步骤指定一个PRIMARY KEY条件约束：1. 在 设计数据表 窗口中，按一下某列中的储存格来选择某个数据行，或

28、按住CTRL键，按一下数据行名称左侧的灰色方块以同时选择多个数据行。 2. 在所选的一个数据行上按右钮，在快捷菜单中选择 设定主索引键 。设定为主索引键的数据行左边会出现一把小钥匙。在把SSN数据行设定为主索引键后，显示的窗口如图16-13所示。我们也已经删除了SSN数据行的UNIQUE条件约束，因为并不需要在同一数据行同时具有UNIQUE条件约束和PRIMARY KEY条件约束。图16-13 在设计数据表窗口中设定PRIMARY KEY条件约束3. 如果要把PRIMARY KEY条件约束移动到另一数据行，只需要把新数据行设定为主索引键即可。您并不一定要先移除原来的主索引键，SQL Serve

29、r将为您删除和重新建立PRIMARY KEY索引。您也可以在 属性 窗口中修改PRIMARY KEY索引。按一下工具列的 储存 按钮储存操作后，修改就会生效。 _说明 如果修改了包含数据的数据表中的PRIMARY KEY条件约束，重新建立索引可能会花费一定时间。若数据表中包含大量数据，欲对索引作较大的修改，例如变更数据行或丛集状态，最好在数据库的非使用高峰期进行这种操作。_外部索引键要使用Enterprise Manager建立或修改FOREIGN KEY条件约束，可使用 设计数据表 窗口或建立与外部索引键有关联性的数据库图表。最好在建立数据表时建立外部索引键关联性。下面的例子将解释这个原因。

30、首先，会学习如何使用 设计数据表 来建立FOREIGN KEY条件约束。我们将利用本章前面建立的两个数据库数据表items和inventory来设定外部索引键关键性。我们会重新建立一个有PRIMARY KEY条件约束的item数据表，只是这次的item_id数据行没有IDENTITY性质。因为我们需要一个item_id已经被更新的范例，而拥有IDENTITY性质的数据行需要较多的步骤才能进行更新。我们也会重新建立一个没有FOREIGN KEY条件约束的inventory数据表，以方便我们稍后加入FOREIGN KEY条件约束。要建立上述的两个数据表，请详以下的陈述式：CREATE TABLE

31、items(item_name char(15) NOT NULL,item_id smallint NOT NULL,price smallmoney NULL,item_desc varchar(30) NOT NULL DEFAULT none,CONSTRAINT PK_item_id PRIMARY KEY (item_id)GOCREATE TABLE inventory(store_id tinyint NOT NULL,item_id smallint NOT NULL,item_quantity tinyint NOT NULL)GO规则对象使用CHECK条件约束的另一种方式是建立规则对象用T-SQL建立规则对象例如USE MyDBGOCREATE RULE price_rule AS(price >= .01 AND price <= 500.00)GOsp_bindrule price_rule, items.price, futureonlyGO要解除系结并删除该关联，使用下面的陈述式:sp_unbindrule items.priceGODROP RULE price_ruleGO