数据完整性
约束的形式
下边通过一个总体说明约束怎样保证数据完整性的:
名称 |
作用 |
体现的完整性 |
Primary key |
定义外键,保证被设定的列内不出现重复值 |
实体完整性 |
Unique |
不出现重复 |
实体完整性 |
Foreign key |
定义外键,保证表和表之间的一致性 |
参照完整性 |
Check |
定义列中数据的范围 |
自定义完整性 |
default |
为列的数据设定默认值 |
自定义完整性 |
1.主键
一个表中只能有一个主键,定义主键可以设定,也可以在代码中需要的时候加在相应的字段
2.unique(不重复)
数据表中非主键列在各行记录中不能出现相同的非空值,这时就需要有unique的特性,用法和主键一样
3.外键
是不同的表之间的关系,当其中有一些数据存在关系是可以建立外键,(可以避免出错)
用法:[constraint 约束名] foreign key (列名) reference 表名(列名)
4.check
限定列之间数据的参照关系 ,比如一个表中包含学生的“出生日期”和“入校日期”,入校日期肯定要晚于出生日期,这个时候可以用check来限定
用法:[constraint 约束名] check (关系式)
5.default(默认值)
当有非常多的相同数据要输入到数据表中的时候,可以设定一个默认值,这样减少工作量
用法:[constraint 约束名] default 常量表达式 for 字段名
说明:在没有单独拿出定义约束的时候可以省略“[constraint 约束名]”
规则与默认值的SQL语句
1.规则
规则的用途:验证数据的值是否处于一定的范围内,是否和特定格式相匹配 是否与制定列表中的输入相匹配
-
创建规则:create rule 规则名 as 规则的表达式(如果表达式中含有参数要加上@)
-
使用规则:sp_bindrule 规则名, ‘表名.列名’
-
查看规则:sp_helptext 规则名
-
解除规则: sp_unbindrule 规则名, ‘表名.列名’
-
删除规则:drop rule 规则名组
2.默认值
-
创建默认值:create default 默认名 as 表达式
-
使用默认值:sp_bindefault 默认名, ‘表名.列名’(注意英文字符)
-
查看默认值:sp_helpconstraint 表名
-
解除默认值:sp_unbindefault 默认名, ‘表名.列名’
-
删除默认值:drop_default 默认值名
一、创建一个关于开课学期的规则
每次设置规则前需要在对象资源管理器中选中“XSCJ”为当前数据库。
①打开“SQL Server Management Studio”窗口。
②单击“标准”工具栏上的“新建查询”按钮,打开“查询编辑器”窗口。
③在窗口内直接输入以下语句,创建规则,将“开课学期”列的值约束在1~8之间。
在【实验二】【创建表并输入数据】中可以看到当时通过SSMS设置过CHECK约束:
CREATE RULE kkxq_rule
AS @开课学期>=1 AND @开课学期<=8
GO
④单击“SQL编辑器”工具栏上的“分析”按钮,检查输入的T-SQL语句是否有语法错误。如果有语法错误,则进行修改。
⑤确保无语法错误后,单击“SQL编辑器”工具栏上的“执行”按钮。
⑥在“查询编辑器”窗口内输入以下语句,将所创建的规则绑定到“开课学期”列。
EXEC Sp_bindrule 'kkxq_rule', 'KC.开课学期'
GO
⑦单击“SQL编辑器”工具栏上的“执行”按钮。
⑧在“对象资源管理器”中,展开目标数据库中的“表”节点,鼠标右键单击目标表“KC”,在弹出的快捷菜单中选择“编辑前200行”命令,输入一行新记录,检验“开课学期”列值的约束情况。
查看约束:
因为在【实验二】【创建表并输入数据】中曾经设置过“开课学期”的约束,这里的效果不明显,添加数据检查约束:
注意数据中不要出现空格。
⑨使用SQL语句查看规则:
EXECUTE sp_helptext kkxq_rule
二、创建一个关于性别的规则
①在“查询编辑器”窗口内直接输入以下语句,为XSQK表的“性别”列创建规则,约束其值只能是“男”或“女”。
CREATE RULE sex_rule
AS @性别 in('男','女')
GO
②单击“SQL编辑器”工具栏上的“执行”按钮。
③在“查询编辑器”窗口内输入以下语句,将所创建的规则绑定到“性别”列。
EXEC Sp_bindrule 'sex_rule','XSQK.性别'
④单击“SQL编辑器”工具栏上的“执行”按钮。
⑤在“对象资源管理器”中,展开目标数据库中的“表”节点,鼠标右键单击目标表“XSQK”,在弹出的快捷菜单中选择“编辑前200行”命令,输入一行新记录,检验“性别”列值的约束情况。
这里查看【实验二】【创建表并输入数据】,当时并没有对性别进行约束。所以这里通过查看性别的属性进行检验:
在“常规”与“CHECK约束”并不能看到创建的规则,说明上面的查看方式也不对。这里依然通过添加数据检查约束:
⑥使用SQL语句查看规则:
EXECUTE sp_helptext sex_rule
三、创建一个关于学分的规则
①在“查询编辑器”窗口内直接输入以下语句,创建规则,要求“学分”列的值只能输入1~6之间的数字。实现步骤如下所述。
CREATE RULE xf_rule
AS @学分 like '[1-6]'
GO
②单击“SQL编辑器”工具栏上的“执行”按钮。
③在“查询编辑器”窗口内输入以下语句,将所创建的规则绑定到“学分”列。
EXEC sp_bindrule 'xf_rule','KC.学分'
GO
④单击” 执行”按钮。
⑤在“对象资源管理器”中,展开目标数据库中的“表”节点,鼠标右键单击目标表“KC”,在弹出的快捷菜单中选择“编辑前200行或者打开表”菜单项,输入一行新记录,检验“学分”列值的约束情况。
通过添加数据检查约束:
这里因为前五条数据是以前创建的,不受新规则影响。从“107”课程的“70”学分可以看出约束生效。
改进:针对实验五创建的数据将SQL语言改为如下,显示效果更佳。
CREATE RULE xf_rule
AS @学分 like '[10-60]'
GO
⑥使用SQL语句查看规则:
EXECUTE sp_helptext xf_rule
总结
规则和约束都可以保证数据完整性,二者的区别:
1:约束是基于表的操作
规则和默认值在创建了以后,他不属于某个表,只有在绑定了命令了以后才会发生关系。
2:一个约束只能对一个表的一个列产生作用,
规则和默认值可以绑定到多个表多个列中。
Reference
SQL Server学习进程(六)–数据完整性(规则、默认、完整性约束)/文CSDN@f斗牛士
数据库SQL server规则的创建、查看、修改和规则的绑定与松绑、删除/文博客园@Stay Hungry, Stay Foolish
数据库——怎样实现数据完整性/文CSDN@窦孟园