开发者社区> 嗯哼9925> 正文

SQL Server-数据库架构和对象、定义数据完整性(二)

简介:
+关注继续查看

前言

本节我们继续SQL之旅,本节我们如题来讲讲一些基本知识以及需要注意的地方,若有不妥之处,还望指出,简短的内容,深入的理解,Always to review the basics。

数据库架构和对象

数据库包含架构,而架构又包含对象,架构可以看做是表、视图、存储过程等对象的容器。架构是一个命名空间,它被用做对象名称的前缀,比如在Cnblogs的架构中有一个名称为Blogs的表,此时我们用架构式限定式名称(即两部分式对象名称)所以Blogs表示为Cnblogs.Blogs。如果我们引用对象时省略了架构名称,SQL Server将会检查对象是否存在用户的默认架构中,如果不是则检查是否存在dbo架构中,当我们创建数据库时,在用户没有显式地指定一个其他架构时,数据库会自动dbo架构作为我们默认的架构。微软也建议在代码中引用对象时始终用【两部分式】对象名称,基于此我们推荐的建议时在引用对象时建议:在代码中始终使用架构限定式的对象名称即两部分式名称。

定义数据完整性

关系模型最大好处则是我们能够自定义数据完整性,同时数据完整性是关系模型不可或缺的一部分,什么是数据完整性,说的通透一点则是对数据进行声明式约束,在SQL Server中声明式约束包括:主键约束、唯一键约束、外键约束、检查约束、默认约束。下面我们一一来介绍这几个约束。

主键约束

下面首先来创建一个表:

CREATE TABLE Blogs
(
 BlogId INT NOT NULL,
 BlogName VARCHAR(max) NOT NULL
);

主键约束用来强制行的唯一性,上述我们无法表示行的唯一性,现在我们添加约束来强制行的唯一性,用PRIMARY KEY约束如下。

ALTER TABLE dbo.Blogs
ADD CONSTRAINT pk_constraint_blogId PRIMARY KEY(BlogId)

在键文件夹中则生成对列BlogId的约束即升级为主键,如下:

当对主键插入重复数据时会提示插入重复键失败,违反约束。为了强制逻辑主键约束的唯一性,SQL Server会在后台创建一个唯一索引,唯一索引是SQL Server为了强制唯一性而使用的一种物理机制,索引(不一定是唯一索引)是为了加速查询,避免不必要的全表扫描。

唯一约束 

唯一约束强制行的唯一性,允许我们在自己的数据库中实现关系模型的备用键概念。它与主键不同,可以在同一个表中定义多个唯一约束同时允许多个NULL标记(类似NULL标记彼此不同),但是SQL Server拒绝重复NULL标记(类似两个NULL标记彼此相等)通过UNIQUE来约束。如下所示对BlogName进行唯一约束。

ALTER TABLE dbo.Blogs
ADD CONSTRAINT uq_constraint_blogname UNIQUE(BlogName)

此时添加唯一约束结果如下

一个个尝试发现居然对字符串和文本类型无法添加唯一约束,涨知识了,不知道为何不能添加唯一约束(学到后面才发现索引字节最大为900,所以这里我们取的max会出错,而不是针对于类型)。

通过上述对主键约束和唯一约束的讲解,我们就搞清楚主键约束和唯一约束了呢?博主看的是SQL Server2012基础教程,教程就讲到这里结束,至此我是还没弄清楚,主键约束和唯一约束到底应该怎样用以及主键约束和唯一约束有什么区别?

(1)对键添加主键约束,那么能不能在此基础上添加唯一约束呢?

我们在上述已经添加BlogId为主键约束的基础上来添加唯一约束,如下

ALTER TABLE dbo.Blogs
ADD CONSTRAINT uq_constraint_blogId UNIQUE(BlogId)

通过上述我们知道对同一列既可以添加主键约束也可以添加唯一约束

(2)上述基础教程中也讲到唯一性约束的列可以允许多个NULL标记,真的是这样?我们看看另外一种情况

我们创建如下表

create table test (
Id INT NOT NULL,
NAME VARCHAR(max) NOT NULL
)

接下来对Id约束为唯一约束。

ALTER TABLE test
ADD CONSTRAINT UNQ UNIQUE(Id)

此时我们对Id添加一个NULL试试看,结果可以插入还是不可以呢?

INSERT INTO TEST VALUES(NULL,'B')

不是唯一约束的列可以为NULL么,难道教程出错了或者sb翻译出错了么,这事我们应该看看定义表时列Id是不能为NULL的,所以到这里我们的疑问算是结束了,唯一约束的列是可以为NULL的。

(3)主键约束和唯一约束的区别?

主键约束:通过对列强制唯一性,此时主键在列上创建一个聚集索引且主键不能为空。

唯一约束:通过对列强制唯一性,此时在列上创建的唯一键为非聚集索引,唯一约束仅仅允许一个NULL值。

二者最大区别在于:主键约束强调的是行的唯一性来标识行,不允许重复,而唯一约束强调的是列的唯一性不允许重复。 

(4)主键约束和唯一约束都可以建立唯一索引

【1】唯一索引通过主键约束和唯一约束都可以创建。

  如果表中不存在聚集索引的话并且我们没有明确指定一个非聚集索引的话,通过主键约束将自动创建一个唯一聚集索引。

  当创建唯一约束时,默认情况下一个非聚集索引会被创建来强制一个唯一约束,如果在表中聚集索引不存在的话,我们可以指定一个聚集索引。

【2】唯一约束和唯一索引区别

我们接下来创建一个表,如下:

CREATE TABLE test
(
 Id INT NOT NULL PRIMARY KEY,
 Code INT
)

首先我只对Code创建唯一索引

CREATE UNIQUE INDEX uq_ix ON dbo.test(Code)

此时我们再在Code列上添加唯一约束:

ALTER TABLE StudyTest.dbo.test
ADD CONSTRAINT uq_nonclster_ix UNIQUE(Code)

此时我们在索引文件夹下可以看到所创建的唯一索引和唯一约束所创建的唯一非聚集索引

看起来二者都是唯一非聚集索引,只是图标不一样而已,二者应该是一样的吧

(5)唯一索引和唯一约束的区别在哪里?唯一约束能替代唯一索引吗?

【1】返回错误码不同

当我们插入数据时,唯一索引返回错误代码为2601

唯一约束返回的错误代码为2627

【2】唯一约束不能筛选,而唯一索引能进行筛选,如下

CREATE UNIQUE NONCLUSTERED INDEX uq_code_filter
ON test(Code) WHERE Code is not null;

总结:上述只是表示二者在使用上的不同,对于唯一约束和唯一索引并没有什么很大的差异,同时对于唯一约束和唯一索引在查询性能上也没有很大的不同,对于唯一约束我们一直强调的是数据完整性,对列进行唯一约束保证其值不能重复,这同时对于建立索引查询时性能会有显著的提升。

外键约束 

外键约束也用来强制数据完整性,外键的目的是限制在外键列中允许的值主要存在于那些被引用列中。下面我们来演示外键约束,我们创建如下雇员表和部门表:

复制代码
USE SQLStudy;

IF OBJECT_ID('dbo.Department','U') IS NOT NULL
    DROP TABLE dbo.Department

CREATE TABLE [dbo].[Department] (
    [DepartmentID]      INT NOT NULL IDENTITY,
    [DepartmentName]    VARCHAR(50)
)
GO

CREATE TABLE [dbo].[Employee] (
    [EmployeeID]     INT NOT NULL IDENTITY,
    [FirstName]      VARCHAR(50),
    [LastName]       VARCHAR(50),
    [DepartmentID]   INT
)
复制代码

由上我们知道雇员表是依赖于部门表,一个雇员到底是在哪个部门呢?所以此时雇员表中的部门Id应该是部门表中部门Id的外键,接下来我们进行外键约束,如下:

ALTER TABLE [dbo].[Employee]
ADD CONSTRAINT [FK_Employee_Department]
FOREIGN KEY ( [DepartmentID] ) REFERENCES [dbo].[Department] ( [DepartmentID] )

此时执行完你会发现如下错误:

现在我们知道外键可不是随便就能建立的,为什么会出现我们引用部门表并将其雇员表中部门Id作为外键约束的错误呢?通过上述错误我们知道在引用表即部门表中没有其匹配的主键或候选键,这是指的什么,它的意思是引用表中的外键必须是被引用表中的完整主键,而不是作为被引用表的一部分,说的更加明确一点则是被引用表即部门表中的部门Id应该是主键,在这里我们未对部门表中部门Id进行主键约束而导致如上错误。我们添加主键约束即可

ALTER TABLE [dbo].[Department]
ADD CONSTRAINT [PK_Department] PRIMARY KEY ( [DepartmentID] )
GO

此时外键约束才算建立完成。到这里其实还存在一种可能,当我们需要引用的表中已经存在一个主键,而不是由外键引用的列,此时部门表中的Id不是作为主键,而我们雇员表中的部门Id又需要将部门Id作为外键约束,这个时候我们只需要在部门表中部门Id上创建唯一或者唯一约束即可。

复制代码
CREATE UNIQUE INDEX [IX_DepartmentID]
ON [dbo].[Department] ( [DepartmentID] )
GO

ALTER TABLE [dbo].[Employee]
ADD CONSTRAINT [FK_Employee_Department]
FOREIGN KEY ( [DepartmentID] ) REFERENCES [dbo].[Department] ( [DepartmentID] )
GO
复制代码

或者唯一约束

复制代码
CREATE UNIQUE INDEX [IX_DepartmentID]
ON [dbo].[Department] ( [DepartmentID] )
GO

ALTER TABLE [dbo].[Employee]
ADD CONSTRAINT [FK_Employee_Department]
FOREIGN KEY ( [DepartmentID] ) REFERENCES [dbo].[Department] ( [DepartmentID] )
GO
复制代码

Check约束 

Check约束定义一个谓词,要插入到表中的行或者被修改的行必须满足此要求。

比如在雇员表中再添加一个薪水字段,很显然薪水必须为正值,此时我们则可以像如下进行Check约束

ALTER TABLE dbo.Employees
 ADD CONSTRAINT CHK_Employees_salary
 CHECK(salary > 0.00)

如果试图插入非正值,将会被数据库所拒绝。我们需要注意的是Check约束只是对于结果为false才会拒绝,如果结果为True或者UNKNOWN是会被接受,即当结果为NULL时也会插入或者修改成功。

默认约束

默认约束无非就是当建立表时给定一个默认值,常见的是在表中存在添加数据的日期这一列,此时我们完全给定一个默认值,取当前的日期。默认约束用DEFAULT关键字表示。例如如下:

ALTER TABLE dbo.Employees
 ADD CONSTRAINT DFT_Employees_updateTime
DEFAULT(GETDATE()) FOR UpdateTime

总结

本节我们详细讲解了主键约束和唯一约束这一块,其余相对比较简单,算是略过,到此结束,下节再会。 




本文转自Jeffcky博客园博客,原文链接:http://www.cnblogs.com/CreateMyself/p/6104345.html,如需转载请自行联系原作者

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
Flink1.13架构全集| 一文带你由浅入深精通Flink方方面面(三)SQL篇(四)
Flink1.13架构全集| 一文带你由浅入深精通Flink方方面面(三)SQL篇
99 0
Flink1.13架构全集| 一文带你由浅入深精通Flink方方面面(三)SQL篇(三)
Flink1.13架构全集| 一文带你由浅入深精通Flink方方面面(三)SQL篇
169 0
Flink1.13架构全集| 一文带你由浅入深精通Flink方方面面(三)SQL篇(二)
Flink1.13架构全集| 一文带你由浅入深精通Flink方方面面(三)SQL篇
128 0
Flink1.13架构全集| 一文带你由浅入深精通Flink方方面面(三)SQL篇(一)
Flink1.13架构全集| 一文带你由浅入深精通Flink方方面面(三)SQL篇
91 0
高性能分布式No SQL数据库Aerospike(一)——介绍和架构
高性能分布式No SQL数据库Aerospike(一)——介绍和架构
205 0
MySQL的逻辑架构--逻辑架构剖析、SQL执行流程、数据库缓冲池(buffer pool)
MySQL的逻辑架构--逻辑架构剖析、SQL执行流程、数据库缓冲池(buffer pool)
75 0
MyCat - 架构剖析 - 核心技术之 SQL 路由实现 | 学习笔记
快速学习 MyCat - 架构剖析 - 核心技术之 SQL 路由实现
60 0
【数据库专题】SQL架构题 620. 有趣的电影
【数据库专题】SQL架构题 620. 有趣的电影
39 0
MySQL逻辑架构 - SQL语句的执行都经历了哪些步骤
大体来说,MySQL 可以分为 Server层 和 存储引擎层两部分。 Server 层包括连接器、查询缓存、分析器、优化器、执行器,涵盖MySQL的大多数核心服务功能,以及所有的内置函数(如日期、时间、数学和加密函数等),所有跨存储引擎的功能也在这一层实现,包括 存储过程、触发器、视图等。 存储引擎层负责数据的存储和提取。包括 MySQL 常见的存储引擎,包括 MyISAM、InnoDB 和 Memory 等,最常用的是 InnoDB,也是现在 MySQL 的默认存储引擎。 存储引擎也可以在创建表的时候手动指定,使用如下语句:
82 0
+关注
嗯哼9925
文章
问答
视频
文章排行榜
最热
最新
相关电子书
更多
用SQL做数据分析
立即下载
阿里云流计算 Flink SQL 核心功能解密
立即下载
Comparison of Spark SQL with Hive
立即下载