网站导航网学 原创论文 原创专题 网站设计 最新系统 原创论文 论文降重 发表论文 论文发表 UI设计定制 论文答辩PPT格式排版 期刊发表 论文专题
返回网学首页
网学原创论文
最新论文 推荐专题 热门论文 论文专题
当前位置: 网学 > 交易代码 > SQL语法 > 正文

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

论文降重修改服务、格式排版等 获取论文 论文降重及排版 论文发表 相关服务

【网学网提醒】:网学会员编辑为广大网友搜集整理了:SQL建立与使用默认值、条件约束及规则绩等信息,祝愿广大网友取得需要的信息,参考学习。


    SQL建立与使用默认值、条件约束及规则
    1、默认值
    使用CREATETABLE建立默认值
    USEMyDB
    CREATETABLEMyTABLE
    (
    columnAchar(15)NULLDEFAULT'n/a',
    columnBintNULLDEFAULT0
    )
    GO
    
    使用ALTERTABLE命令可以修改数据行中的默认值定义或新增一数据行。若要更改已经定义的默认值数据行,首先必须删除已经有的默认值,然后新增一个新的默认值
    如果用CREATETABLE命令建立未命名的默认值,SQLServer将自动替默认值命名。要知道SQLServer为默认值分配了什么名字,以便可以使用T-SQL删除它,您可执行sp_help程序如下:
    USEMyDB
    GO
    sp_helpMyTable
    GO
    假设我们要把columnA的默认值从n/a改成notapplicable。记住首先必须删除存在的默认值然后再新增一个新的。下述命令即可删除默认值:
    ALTERTABLEMyTable
    DROPCONSTRAINTDF_MyTable_columnA_2B3F6F97
    现在您可以使用下述命令新增一个默认值,这次由我们自己命名:
    ALTERTABLEMyTable
    ADDCONSTRAINTDF_MyTable_columnA
    DEFAULT"Notapplicable"FORcolumnA
    GO
    当变更已存在的默认值时,所有现存的列将保持原始的默认值。只有新插入的列会使用新的默认值。
    用ALTERTABLE命令为已有的数据表新增完整的新数据行,如下所示:
    ALTERTABLEMyTable
    ADDcolumnCtinyintNOTNULLDEFAULT13
    GO
    用默认值而不是NULL插入已存在的列,则应该使用DEFAULT中的WITHVALUES选项,如下所示:
    ALTERTABLEMyTable
    ADDcolumnCtinyintNULLDEFAULT13WITHVALUES
    GO
    WITHVALUES命令会强行使MyTable中所有现存列的新数据行接受默认值13来代替原来的NULL值。
    CREATEDEFAULT和sp_bindefault
    
    如果要在不同的数据表中使用相同的默认值数据行,此方法则较为有效。
    使用CREATEDEFAULT的语法如下:
    CREATEDEFAULTdefault_nameASconstant_expression
    sp_bindefault的语法如下:
    sp_bindefault'default_name'table.column|user_defined_datatype
    [",futureonly"]
    示例:
    USEMyDB
    GO
    CREATEDEFAULTDF_not_applicableAS'n/a'
    GO
    sp_bindefault"DF_not_applicable","MyTable.columnA"
    GO
    如果没有指定futureonly,SQLServer将默认值系结到所有已经存在的和新建立的使用者自订类型的数据行上
    例如,让我们建立一个名称为area_code的使用者自订型别和名称为DF_area_code的默认值对象,其值为786;然后系结默认值到该使用者自订数据型别上。因为这是新的使用者自订数据型别,因此目前还没有数据行,也就不需要futureonly选项
    sp_addtype"area_code","char(3)","NOTNULL"
    GO
    CREATEDEFAULTDF_area_codeAS786
    GO
    sp_bindefault"DF_area_code","area_code","futureonly"
    GO
    要检视预设对象的数据型别,可使用sp_help系统程序
    sp_unbindefault
    
    例如要解除MyTable中与col
    umnA数据行系结的默认值:sp_unbindefault如下:
    sp_unbindefault"MyTable.columnA"
    GO
    从使用者自订的数据型别area_code中解除系结默认值:
    sp_unbindefault"area_code"
    GO
    当执行以上程序,所有已经由使用者自订数据型别area_code的默认值属性将会同时移除。
    同样的,只要在不删除预设对象的情形下,您可以随意地解除或系结某个数据行的预设。使用DROPDEFAULT陈述式,可以完全删除一个预设对象
    如下所示:
    DROPDEFAULTDF_area_code
    GO
    一旦删除了预设对象,就无法再取回。如果要再次使用,必须使用CREATEDEFAULT重新建立对象。
    
    在默认值储存格中输入字符串,必须放在单引号中,否则储存时会显示SQLServer的错误讯息。
    2条件约束
    
    条件约束用于自动维护数据的完整性。举个例子,您可以将一个整数数据行条件约束在1到100的范围内,那么超出此范围的数值则无法被接受
    条件约束的五种类型为NOTNULL、UNIQUE、PRIMARYKEY、FOREIGNKEY和CHECK
    使用T-SQL建立和修改条件约束
    
    NOTNULL
    
    NOTNULL条件约束相当简单
    UNIQUE
    UNIQUE条件约束用以确保一个或多个数据行中没有重复的数值
    要用T-SQL为数据表建立UNIQUE条件约束,须执行CREATETABLE或ALTERTABLE命令
    例如
    CREATETABLEcustomer
    (
    first_namechar(20)NOTNULL,
    mid_initchar(1)NULL,
    last_namechar(20)NOTNULL,
    SSNchar(11)NOTNULLUNIQUECLUSTERED,
    cust_phonechar(10)NULL
    )
    GO
    例子
    CREATETABLEcustomer
    (
    first_namechar(20)NOTNULL,
    mid_initchar(1)NULL,
    last_namechar(20)NOTNULL,
    SSNchar(11)NOTNULLUNIQUECLUSTERED,
    cust_phonechar(10)NULL,
    CONSTRAINTUQ_full_nameUNIQUENONCLUSTERED(first_name,mid_init,last_name)
    )
    GO
    新增数据行条件约束和数据表条件约束的两组命令:
    ALTERTABLEcustomer
    ADDCONSTRAINTUQ_ssnUNIQUECLUSTERED(SSN)
    GO
    ALTERTABLEcustomer
    ADDCONSTRAINTUQ_full_nameUNIQUENONCLUSTERED(first_name,mid_init,last_name)
    GO
    要用T-SQL来修改数据行或数据表中已有的UNIQUE条件约束,必须先删除条件约束再重新建立
    主索引键
    
    CREATETABLEcustomer
    (
    first_namechar(20)NOTNULL,
    mid_initchar(1)NULL,
    last_namechar(20)NOTNULL,
    SSNchar(11)PRIMARYKEY,
    cust_phonechar(10)NULL
    )
    GO
    另一种可行的方法是以增加CONSTRAINT关键词来命名。使用下面的命令将主索引键命名为PK_SSN:
    CREATETABLEcustomer
    (
    first_namechar(20)NOTNULL,
    mid_initchar(1)NULL,
    last_namechar(20)NOTNULL,
    SSNchar(11)CONSTRAINTPK_SSNPRIMARYKEY,
    cust_phonechar(10)NULL
    )
    GO
    您也可以在定义了所有数据表的数据行后,再指定PRIMAR
    YKEY条件约束。数据行名称必须在括号中,并在CONSTRAINT后指定,语法如下所示:
    CREATETABLEcustomer
    (
    first_namechar(20)NOTNULL,
    mid_initchar(1)NULL,
    last_namechar(20)NOTNULL,
    SSNchar(11),
    cust_phonechar(10)NULL,
    CONSTRAINTPK_SSNPRIMARYKEY(SSN)
    )
    GO
    以下为替customer资料表新增PRIMARYKEY
    ALTERTABLEcustomer
    ADDCONSTRAINTPK_SSNPRIMARYKEYCLUSTERED(SSN)
    GO
    要删除PRIMARYKEY条件约束,须使用ALTERTABLE命令和DROPCONSTRAINT陈述式
    ALTERTABLEcustomer
    DROPCONSTRAINTPK_SSN
    GO
    注意,只有在DROPCONSTRAINT的陈述式中,需要条件约束名称。要使用T-SQL命令修改数据表中现存的PRIMARYKEY条件约束,必须先使用ALTERTABLE...DROPCONSTRAINT删除现存条件约束和ALTERTABLE...ADDCONSTRAINT陈述式来新增条件约束以修改资料表。
    外部索引键
    
    首先建立一个数据表,命名为items,其中的item_id数据行具主索引键,如下所示:
    CREATETABLEitems
    (
    item_namechar(15)NOTNULL,
    item_idsmallintNOTNULLIDENTITY(1,1),
    pricesmallmoneyNULL,
    item_descvarchar(30)NOTNULLDEFAULT'none'
    CONSTRAINTPK_item_idPRIMARYKEY(item_id)
    )
    GO
    建立inventory的数据表,其中有称为FK_item_id的外部索引键,此外部索引键引用items数据表的item_id
    如下所示:
    CREATETABLEinventory
    (
    store_idtinyintNOTNULL,
    item_idsmallintNOTNULL,
    item_quantitytinyintNOTNULL,
    CONSTRAINTFK_item_idFORGIENKEY(item_id)
    REFERENCESitems(item_id)
    )
    GO
    下面先删除inventory数据表的旧条件约束,然后新增条件约束的命令:
    ALTERTABLEinventory
    DROPCONSTRAINTFK_item_id
    GO
    ALTERTABLEinventory
    ADDCONSTRAINTFK_item_idFOREIGNKEY(item_id)
    REFERENCESitems(item_id)
    GO
    当您在现存资料行中新增FOREIGNKEY条件约束,SQLServer会检查数据表中现存的数据列,以确保除NULL值外,外部索引键数据行的值符合参照资料表的PRIMARYKEY条件约束或UNIQUE条件约束。当建立FOREIGNKEY条件约束时,可以使用ALTERTABLE的WITHNOCHECK选项,那么SQLServer就不会去验证现有的值,如下所示:
    ALTERTABLEinventory
    WITHNOCHECKADDCONSTRAINTFK_item_id
    FOREIGNKEY(item_id)
    REFERENCESitems(item_id)
    GO
    WITHNOCHECK选项可避免SQL检查数据表中现存列的值。如此,无论现有值为何,都可以新增条件约束到数据表上。新增条件约束后,外部索引键的完整性也会增强。
    ________________________________________
    注意
    使用WITHNOCHECK选项时要小心。因为当您预计更新现存数据,但是现存数据中却包含与条件约束冲突的值,您将无法更新现存数据。
    ________________________________________
    您也可以控制是否启用FOREIGN
    KEY条件约束。NOCHECK关键词表示忽略条件约束;CHECK关键词表示条件约束生效。
    ALTERTABLEinventory
    NOCHECKCONSTRAINTFK_item_id--使条件约束无效
    GO
    --在此插入您需要的数据列
    ALTERTABLEinventory
    CHECKCONSTRAINTFK_item_id--重新回复条件约束
    GO
    ________________________________________
    说明
    尽量不要插入与FOREIGNKEY条件约束冲突的资料行。否则可能导致将来数据表的有冲突的数据列无法被更新。
    ________________________________________
    CHECK
    
    CHECK条件约束用于限制数据行中值的允许范围。条件约束中指定的布尔(Boolean)搜寻条件传回的是TRUE时,在数据行中插入或修改的值才算有效。例如,如果我们要限制items数据表的price数据行允许值的可能范围在$0.01到$500.00之间,应该使用下面的陈述式:
    CREATETABLEitems
    (
    item_namechar(15)NOTNULL,
    item_idsmallintNOTNULLIDENTITY(1,1),
    pricesmallmoneyNULL,
    item_descvarchar(30)NOTNULLDEFAULT'none'
    CONSTRAINTPK_item_idPRIMARYKEY(item_id),
    CONSTRAINTCK_priceCHECK(price>=.01AND
    price<=500.00)
    )
    GO
    注意,我们在price数据行中允许NULL,且在数据行中有CHECK条件约束。由于SQLServer可以辨别NULL值和其它型别的值,所以尽管有CHECK条件约束,price数据行仍然可允许为NULL值。同时需要注意的是,我们将这个条件约束命名为CK_price。我们之前曾看到,将条件约束命名在稍后即可简单地用T-SQL来删除和重新建立条件约束。例如,把值的范围修改为从$1.00到$1000.00之间,可以使用下面的陈述式:
    ALTERTABLEitems
    DROPCONSTRAINTCK_price
    GO
    ALTERTABLEitems
    ADDCONSTRAINTCK_priceCHECK(price>=1.00AND
    price<=1000.00)
    GO
    第二个ALTERTABLE命令应该与第一次为现存的items数据表新增条件约束时所使用的命令相同。为现存资料表新增CHECK条件约束和新增FOREIGNKEY条件约束所遵守的规则是一样的。所有现存的列将会根据条件约束来检查,如果所有回传的值不是TRUE,则无法将条件约束将新增到数据表中,而且SQLServer将回传错误讯息:指出ALTERTABLE陈述式与CHECK条件约束冲突。如果一定要新增条件约束,使用WITHNOCHECK指定现存的数据列不生效,而将来插入和修改的资料列得以生效。
    ________________________________________
    注意
    不建议使用WITHNOCHECK,因为将来可能无法更新不符合条件约束的列。
    ________________________________________
    下面是增加CK_price条件约束时使用WITHNOCHECK的一个例子:
    ALTERTABLEitems
    WITHNOCHECKADDCONSTRAINTCK_price
    CHECK(price>=1.00ANDprice<=1000.00)
    GO
    和FOREIGNKEY条件约束一样,在ALTERTABLE中使用CHECK和NOCHECK关键词也
    可以控制CHECK条件约束是否生效。您可能想用这种方法插入一个超出指定范围但依然有效的价格。下面的例子先停用CK_price条件约束,然后再使其生效:
    ALTERTABLEitems
    NOCHECKCONSTRAINTCK_price--使条件约束无效
    GO
    --在此插入资料列
    GO
    ALTERTABLEitems
    CHECKCONSTRAINTCK_price--重新启用条件约束
    GO
    ________________________________________
    说明
    只有CHECK和FOREIGNKEY类型的条件约束可以用这种方式来控制是否生效。
    ________________________________________
    用EnterpriseManager建立和修改条件约束
    
    这部分将学习如何用EnterpriseManager设计数据表窗口来建立、修改和删除条件约束,以及在FOREIGNKEY条件约束的情况下,建立数据库图表(在第15章介绍了如何建立数据库图表)。使用EnterpriseManager来建立新资料表或编辑现存数据表时,将显示设计数据表窗口。要建立新数据表,在EnterpriseManager的左边窗格中展开服务器和数据库数据夹,在数据表上按右钮,从快捷菜单中选择新增/数据表。要显示现存数据表的设计数据表窗口,首先在左边窗格中找出展开数据表,在右边窗格中的数据表名称上按右钮,然后从快捷菜单中选择设计数据表。
    允许NULL值
    
    要指定数据行中是否允许NULL值,在设计数据表窗口中的是否允许NULL标题下简单地选取或清除适当的复选框即可。您可以在建立数据表或修改数据表时设定这一选项。关于允许NULL值的规则请参见第10章。图16-11显示了本章前面〈用T-SQL建立和修改数据表〉我们曾经建立的customer数据表的设计数据表窗口。您可以看到mid_init和cust_phone两个数据行允许NULL值,但其它三数据行不允许NULL值。
    
    
    图16-11customer数据表设计数据表窗口的「是否允许NULL」数据行的设定
    UNIQUE
    
    1.要使用EnterpriseManager来建立和修改条件约束,请遵循以下步骤:
    在设计数据表窗口中,在工具列中选择数据表索引属性按钮(存盘按钮右边的按钮),在属性对话框中选择索引/索引键卷标页。图16-12显示了customer数据表的属性对话框中的索引/索引键卷标页。
    使用下面的命令来建立这张数据表。数据表的SSN数据行包括一条作为丛集索引的UNIQUE条件约束(SQLServer自动替索引命名为UQ_customer_398D8EEE;您现在应可明白为条件约束及索引特别命名的好处):
    CREATETABLEcustomer
    (
    first_namechar(20)NOTNULL,
    mid_initchar(1)NULL,
    last_namechar(20)NOTNULL,
    SSNchar(11)NOTNULLUNIQUECLUSTERED,
    cust_phonechar(10)NULL
    )
    GO
    
    
    图16-12customer数据表的属性对话框中的索引/索引键卷标页
    2.要建立一个新的UNIQUE条件约束,
    在属性对话框的索引/索引键卷标页上按新增按钮,选择条件约束使用的数据行名称,键入新的条件约束名称,然后选择建立成唯一-UNIQUE复选框。如果要使它作为数据行的丛集索引,在数据表中选择建立成丛集-CLUSTERED复选框,并指定填满因子。如果不要SQLServer定期地自动计算索引数据,则选取选取方块旁的选项。
    3.您可以使用属性对话框修改UNIQUE条件约束。例如,修改条件约束名称、指定要新增到条件约束的数据行、设定丛集索引选项,以及为索引选择填充因子等。(填满因子将在第17章详细介绍。)对条件约束进行修改,完成后按一下关闭按钮,然后在EnterpriseManager中按储存按钮储存修改。
    主索引键
    
    您可以为一或多个数据行指定一个主索引键(PRIMARYKEY)条件约束。按以下步骤指定一个PRIMARYKEY条件约束:
    1.在设计数据表窗口中,按一下某列中的储存格来选择某个数据行,或按住CTRL键,按一下数据行名称左侧的灰色方块以同时选择多个数据行。
    2.在所选的一个数据行上按右钮,在快捷菜单中选择设定主索引键。设定为主索引键的数据行左边会出现一把小钥匙。在把SSN数据行设定为主索引键后,显示的窗口如图16-13所示。我们也已经删除了SSN数据行的UNIQUE条件约束,因为并不需要在同一数据行同时具有UNIQUE条件约束和PRIMARYKEY条件约束。
    
    
    图16-13在设计数据表窗口中设定PRIMARYKEY条件约束
    3.如果要把PRIMARYKEY条件约束移动到另一数据行,只需要把新数据行设定为主索引键即可。您并不一定要先移除原来的主索引键,SQLServer将为您删除和重新建立PRIMARYKEY索引。您也可以在属性窗口中修改PRIMARYKEY索引。按一下工具列的储存按钮储存操作后,修改就会生效。
    ________________________________________
    说明
    如果修改了包含数据的数据表中的PRIMARYKEY条件约束,重新建立索引可能会花费一定时间。若数据表中包含大量数据,欲对索引作较大的修改,例如变更数据行或丛集状态,最好在数据库的非使用高峰期进行这种操作。
    ________________________________________
    外部索引键
    
    要使用EnterpriseManager建立或修改FOREIGNKEY条件约束,可使用设计数据表窗口或建立与外部索引键有关联性的数据库图表。最好在建立数据表时(或者至少在数据插入数据表之前)建立外部索引键关联性。下面的例子将解释这个原因。首先,会学习如何使用设计数据表来建立FOREIGNKEY条件约束。我们将利用本章前面建立的两个数据库数据表items和inventory来设定外部索引键关键性。我们会重新建立一个有PRIMARYKEY条件约束的item数据表(这是我们
    之前使用的),只是这次的item_id数据行没有IDENTITY性质。因为我们需要一个item_id已经被更新的范例,而拥有IDENTITY性质的数据行需要较多的步骤才能进行更新。我们也会重新建立一个没有FOREIGNKEY条件约束的inventory数据表,以方便我们稍后加入FOREIGNKEY条件约束。要建立上述的两个数据表,请详以下的陈述式:
    CREATETABLEitems
    (
    item_namechar(15)NOTNULL,
    item_idsmallintNOTNULL,
    pricesmallmoneyNULL,
    item_descvarchar(30)NOTNULLDEFAULT'none',
    CONSTRAINTPK_item_idPRIMARYKEY(item_id)
    )
    GO
    CREATETABLEinventory
    (
    store_idtinyintNOTNULL,
    item_idsmallintNOTNULL,
    item_quantitytinyintNOTNULL
    )
    GO
    规则对象
    
    使用CHECK条件约束的另一种方式是建立规则对象
    用T-SQL建立规则对象
    
    例如
    USEMyDB
    GO
    CREATERULEprice_ruleAS
    (@price>=.01AND@price<=500.00)
    GO
    sp_bindrule"price_rule","items.price","futureonly"
    GO
    要解除系结并删除该关联,使用下面的陈述式:
    sp_unbindrule"items.price"
    GO
    DROPRULEprice_rule
    GO
    
  • 上一篇资讯: SQL异步执行技术
  • 下一篇资讯: sql应用培训资料
  • 设为首页 | 加入收藏 | 网学首页 | 原创论文 | 计算机原创
    版权所有 网学网 [Myeducs.cn] 您电脑的分辨率是 像素
    Copyright 2008-2020 myeducs.Cn www.myeducs.Cn All Rights Reserved 湘ICP备09003080号 常年法律顾问:王律师