SQL Server数据类型与约束：数据库的坚固基石

本文链接：https://blog.csdn.net/fq1986614/article/details/146153511

引言

在数据库的世界里，SQL Server 就像是一座大型图书馆，里面存放着海量的数据。而数据类型，就如同图书馆书架上不同类型的标签，比如文学类、历史类、科学类等，它定义了数据的种类和特性，规定了数据可以存储的内容和格式。约束则像是图书馆的规章制度，比如书籍不能带出馆外、借阅期限的规定等，它确保了数据的准确性、一致性和完整性，防止无效数据的插入和修改。没有合理的数据类型和约束，数据库中的数据就会像没有整理的图书馆书架，杂乱无章，难以管理和使用。接下来，就让我们深入了解 SQL Server 的数据类型与约束，一起探索如何打造一个高效、可靠的数据库。

SQL Server 数据类型

系统数据类型

整数类型：

- bigint：占用 8 个字节存储空间，可表示范围从\(-2^{63}\)到\(2^{63}-1\) ，适用于存储非常大的整数，比如大型网站的用户访问量统计（假设以秒为单位统计一年的访问次数，这个数字可能会非常大）。

- int：最常用的整数类型，占用 4 个字节，能表示的范围是\(-2^{31}\)到\(2^{31}-1\) ，像一般的用户 ID、订单编号等都可以使用 int 类型。

- smallint：占用 2 个字节，取值范围是\(-2^{15}\)到\(2^{15}-1\) ，如果数据范围相对较小，如学生的年龄（一般不会超过 200 岁），可以使用这个类型，以节省存储空间。

- tinyint：占用 1 个字节，只能表示 0 到 255 之间的无符号整数，比如表示商品的库存状态（0 表示无货，1 - 255 表示有不同数量的库存）。

浮点类型：

- real：占用 4 个字节，可精确到 7 位有效数字，存储范围是\(-3.40E + 38\)到\(-1.18E - 38\)、0 以及\(1.18E - 38\)到\(3.40E + 38\)，常用于对精度要求不是特别高的科学计算或者统计分析，如统计某地区的平均气温。

- float[(n)]：占用 8 个字节，当 n 为 1 - 24 时，等同于 real 类型；n 为 25 - 53 时，可精确到 15 位有效数字，范围是\(-1.79E + 308\)到\(-2.23E - 308\)、0 以及\(2.23E + 308\)到\(1.79E + 308\) ，适合存储非常大或非常小的科学数据，如宇宙中星球之间的距离。

- decimal[(p,s)]和numeric[(p,s)]：这两个类型功能相同，用于存储固定精度和小数位数的数值。p 指定了总位数（包括小数点左边和右边），范围是 1 到 38；s 指定了小数点右边的位数，范围是 0 到 p 。例如，decimal (10, 2) 表示总共有 10 位数字，其中小数部分占 2 位，常用于财务计算，如金额的存储，保证数据的精确性。

二进制类型：

- binary(n)：固定长度的二进制数据，n 的取值范围是 1 到 8000，存储大小为 n 字节，比如存储固定长度的文件标识。

- varbinary(n|max)：可变长度的二进制数据，n 取值范围是 1 到 8000，max 表示最大存储大小为\(2^{31}-1\)字节，常用来存储图片、音频等二进制文件，因为文件大小不固定，所以用可变长度更合适。

- image：可变长度的二进制数据，最大长度为\(2^{31}-1\)字节，和 varbinary (max) 类似，但在 SQL Server 2005 及更高版本中，推荐使用 varbinary (max) 来代替 image 类型存储二进制大对象。

逻辑类型：

- bit：只占用 1 个字节，值只能是 0、1 或 NULL ，用于表示逻辑判断，如订单是否已支付（0 表示未支付，1 表示已支付）。

字符类型：

- char(n)：固定长度的非 Unicode 字符数据，n 取值范围是 1 到 8000。如果输入的数据长度小于 n，系统会自动在后面补空格；如果超过 n，会截断超出部分。比如存储固定长度的身份证号码（18 位），就可以使用 char (18) 。

- varchar(n|max)：可变长度的非 Unicode 字符数据，n 取值范围是 1 到 8000，max 表示最大存储大小为\(2^{31}-1\)字节。存储大小为实际数据长度加 2 个字节，适用于存储长度不固定的文本，如文章摘要，因为摘要长度不固定，使用 varchar 更节省空间。

- nchar(n)：固定长度的 Unicode 字符数据，n 取值范围是 1 到 4000 。由于是 Unicode 编码，每个字符占用 2 个字节，所以能存储更多语言的字符，比如存储包含中文、英文、日文等多语言混合的短文本。

- nvarchar(n|max)：可变长度的 Unicode 字符数据，n 取值范围是 1 到 4000，max 表示最大存储大小为\(2^{31}-1\)字节，存储大小为实际字符个数的两倍加 2 个字节，常用于存储长度不固定的多语言文本，如国际化网站的新闻内容。

- text：可变长度的非 Unicode 字符数据，最大长度为\(2^{31}-1\)字节，不过在 SQL Server 2005 及更高版本中，推荐使用 varchar (max) 代替。

- ntext：可变长度的 Unicode 字符数据，最大长度为\(2^{30}-1\)字节，同样在 SQL Server 2005 及更高版本中，推荐使用 nvarchar (max) 代替。

日期和时间类型：

- date：只存储日期，格式为 “YYYY - MM - DD”，占用 3 个字节，可表示的范围是 “0001 - 01 - 01” 到 “9999 - 12 - 31” ，比如存储员工的入职日期。

- time[(n)]：只存储时间，格式为 “hh:mm:ss [.nnnnnnn]”，n 表示秒的小数部分的精度，取值范围是 0 到 7，默认值为 7 ，占用 5 个字节，可表示的范围是 “00:00:00.0000000” 到 “23:59:59.9999999”，用于记录事件发生的具体时间点，如公交车的到站时间。

- datetime：存储日期和时间，可表示的范围是 “1753 - 01 - 01 00:00:00” 到 “9999 - 12 - 31 23:59:59” ，占用 8 个字节，精确到 3.33 毫秒，常用于记录需要同时包含日期和时间的信息，如订单的创建时间。

- smalldatetime：也存储日期和时间，可表示的范围是 “1900 - 01 - 01 00:00:00” 到 “2079 - 06 - 06 23:59:00” ，占用 4 个字节，精确到 1 分钟，对于时间精度要求不高的场景可以使用，如简单记录会议的开始时间。

- datetime2[(n)]：是 datetime 的改进版本，可表示的范围和 datetime 相同，但精度更高，n 表示秒的小数部分的精度，取值范围是 0 到 7 ，占用 6 到 8 个字节，根据精度不同而不同。

- datetimeoffset[(n)]：除了存储日期和时间，还存储时区偏移量，可表示的范围和 datetime 相同，n 表示秒的小数部分的精度，取值范围是 0 到 7 ，占用 8 到 10 个字节，适用于跨国业务中需要考虑不同地区时间差异的场景。

货币类型：

- money：占用 8 个字节，可表示的范围是\(-922,337,203,685,477.5808\)到\(922,337,203,685,477.5807\) ，精确到货币单位的千分之一，用于存储金额，如商品的价格。

- smallmoney：占用 4 个字节，可表示的范围是\(-214,748.3648\)到\(214,748.3647\) ，精确到货币单位的千分之十，适用于金额较小且对精度要求不是特别高的场景，如小额交易的金额记录。

用户自定义数据类型

用户自定义数据类型是基于系统数据类型创建的，它可以让我们根据实际业务需求，对数据类型进行更有针对性的定义，提高代码的可维护性和重用性。

创建用户自定义数据类型：

- 在 SQL Server 中，可以使用CREATE TYPE语句来创建用户自定义数据类型。例如，假设我们经常需要存储电话号码，电话号码的格式是固定的 11 位数字，可以创建一个自定义数据类型来存储它：

-- 创建基于varchar(11)的自定义数据类型PhoneNumber

CREATE TYPE PhoneNumber FROM varchar(11) NOT NULL;

使用用户自定义数据类型：

创建好自定义数据类型后，就可以像使用系统数据类型一样在表中使用它。比如创建一个客户信息表，其中客户的电话号码字段就可以使用我们刚刚创建的PhoneNumber类型：

-- 创建客户信息表

CREATE TABLE Customer (

CustomerID int IDENTITY(1,1) PRIMARY KEY,

CustomerName nvarchar(50),

Phone PhoneNumber

);

这样，在这个表中，Phone字段的数据类型就是我们自定义的PhoneNumber，它限制了数据必须是 11 位的非空字符串，保证了数据的格式统一。如果插入不符合格式的数据，就会报错。

通过使用用户自定义数据类型，当我们在多个表或者多个存储过程等数据库对象中需要使用相同格式的数据时，只需要修改自定义数据类型的定义，而不需要在每个使用的地方都进行修改，大大提高了代码的可维护性和重用性。同时，它也使得数据库设计更加清晰和规范，更符合业务逻辑。

SQL Server 约束

在 SQL Server 中，约束是保证数据完整性和一致性的重要机制，就像是数据库的守护者，确保数据符合特定的规则和条件。下面详细介绍几种常见的约束。

主键约束

主键约束用于唯一标识表中的每一行记录，就像每个人的身份证号码，是独一无二的。在一个表中，只能有一个主键约束。例如，在员工信息表中，可以将员工编号设置为主键：

-- 创建员工信息表并设置员工编号为主键

CREATE TABLE Employee (

EmployeeID int IDENTITY(1,1) PRIMARY KEY,

EmployeeName nvarchar(50),

Department nvarchar(50)

);

这里EmployeeID列被定义为主键，IDENTITY(1,1)表示该列是自增长的，从 1 开始，每次增加 1 。主键约束的特点是不允许为空值，并且值必须唯一，这保证了表中每一行数据的唯一性，方便对数据进行准确的定位和检索。如果尝试插入两条EmployeeID相同的记录，就会报错，因为违反了主键约束的唯一性。

外键约束

外键约束用于建立两个表之间的关联关系，确保数据表之间的数据符合引用完整性。比如，有一个订单表和一个客户表，订单表中的客户 ID 字段需要关联到客户表中的客户 ID 字段，以确保每个订单都对应一个有效的客户。创建外键约束的语法如下：

-- 创建客户表

CREATE TABLE Customer (

CustomerID int IDENTITY(1,1) PRIMARY KEY,

CustomerName nvarchar(50)

);

-- 创建订单表并添加外键约束

CREATE TABLE OrderTable (

OrderID int IDENTITY(1,1) PRIMARY KEY,

OrderDate datetime,

CustomerID int,

FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)

);

在这个例子中，OrderTable表中的CustomerID字段是外键，它引用了Customer表中的CustomerID字段。这意味着在OrderTable表中插入数据时，如果CustomerID的值在Customer表中不存在，就会违反外键约束，插入操作将失败，从而保证了数据的一致性。例如，如果尝试在OrderTable中插入一条CustomerID为 100（假设Customer表中没有CustomerID为 100 的记录）的订单记录，就会报错。

唯一约束

唯一约束确保列中的值是唯一的，与主键约束类似，但一个表中可以有多个唯一约束，而且唯一约束允许列中存在空值（不过只能有一个空值）。比如，在员工信息表中，员工的电子邮箱地址需要保证唯一，可以添加唯一约束：

-- 创建员工信息表并为电子邮箱地址字段添加唯一约束

CREATE TABLE Employee (

EmployeeID int IDENTITY(1,1) PRIMARY KEY,

EmployeeName nvarchar(50),

Email nvarchar(100),

UNIQUE (Email)

);

这样，如果尝试插入两条Email相同的记录，就会违反唯一约束，操作会失败。唯一约束和主键约束的主要区别在于，主键约束不允许为空值且一个表只能有一个，而唯一约束允许空值且可以有多个。在实际应用中，主键约束常用于唯一标识记录，而唯一约束用于保证某些列的唯一性，比如用户名、身份证号码等在各自列中需要唯一，但不一定适合作为主键的情况。

检查约束

检查约束通过定义一个布尔表达式来限制列中可以接受的值，确保数据符合特定的条件。例如，在员工信息表中，员工的年龄字段需要满足一定的范围，可以添加检查约束：

-- 创建员工信息表并为年龄字段添加检查约束

CREATE TABLE Employee (

EmployeeID int IDENTITY(1,1) PRIMARY KEY,

EmployeeName nvarchar(50),

Age int,

CHECK (Age >= 18 AND Age <= 60)

);

这里CHECK (Age >= 18 AND Age <= 60)表示Age字段的值必须在 18 到 60 之间，如果尝试插入一条Age为 15 或者 70 的记录，就会违反检查约束，插入操作将失败。检查约束可以用于各种需要数据验证的场景，如商品的价格必须大于 0、学生的成绩必须在 0 到 100 之间等，通过检查约束可以有效保证数据的合理性和准确性。

默认约束

默认约束为列提供一个默认值，当插入数据时，如果没有为该列指定值，就会使用默认值。比如，在员工信息表中，员工的入职状态默认为 “在职”，可以添加默认约束：

-- 创建员工信息表并为入职状态字段添加默认约束

CREATE TABLE Employee (

EmployeeID int IDENTITY(1,1) PRIMARY KEY,

EmployeeName nvarchar(50),

EmploymentStatus nvarchar(20) DEFAULT '在职'

);

这样，在插入新员工记录时，如果没有指定EmploymentStatus的值，就会自动使用 “在职” 作为默认值。默认约束可以简化数据输入，减少错误，并且保证数据的一致性。在很多情况下，某些字段有比较固定的初始值，使用默认约束可以避免每次插入数据时都手动输入这些值，提高数据插入的效率。

非空约束

非空约束确保列中不允许为空值，即每一行数据在该列都必须有一个确定的值。比如，在员工信息表中，员工姓名字段不能为空，可以添加非空约束：

-- 创建员工信息表并为员工姓名字段添加非空约束

CREATE TABLE Employee (

EmployeeID int IDENTITY(1,1) PRIMARY KEY,

EmployeeName nvarchar(50) NOT NULL,

Department nvarchar(50)

);

如果尝试插入一条EmployeeName为空的记录，就会违反非空约束，插入操作将失败。非空约束在保证数据完整性方面非常重要，特别是对于一些关键信息，如客户的姓名、订单的编号等，不能为空，否则会影响数据的准确性和后续的业务处理。

数据类型与约束的协同作用

数据类型和约束在 SQL Server 中就像一对默契的搭档，共同确保数据的完整性，它们从不同层面保障数据的质量。

数据类型规定了数据的存储格式和取值范围，而约束则进一步细化了数据的限制条件。例如，在一个员工工资表中，工资字段的数据类型选择decimal(10, 2) ，这就限定了工资数据是具有固定精度和小数位数的数值，总共有 10 位数字，其中小数部分占 2 位。同时，为了确保工资数据的合理性，还可以添加检查约束，如CHECK (Salary >= 0) ，这就保证了工资值必须是大于等于 0 的数值。如果没有数据类型的限制，可能会将字符串类型的数据误插入到工资字段中；而没有检查约束，就可能出现负数工资的不合理情况。

在表与表之间的关系中，数据类型和约束也紧密配合。以订单表和产品表为例，订单表中的产品 ID 字段的数据类型需要与产品表中产品 ID 字段的数据类型一致，这样才能建立有效的外键约束。假设产品表中产品 ID 字段的数据类型是int，那么订单表中产品 ID 字段也必须是int类型，然后可以添加外键约束，如：

-- 创建产品表

CREATE TABLE Product (

ProductID int IDENTITY(1,1) PRIMARY KEY,

ProductName nvarchar(50),

Price decimal(10, 2)

);

-- 创建订单表并添加外键约束

CREATE TABLE OrderTable (

OrderID int IDENTITY(1,1) PRIMARY KEY,

OrderDate datetime,

ProductID int,

FOREIGN KEY (ProductID) REFERENCES Product(ProductID)

);

这样，通过数据类型的一致性和外键约束，保证了订单表中的产品 ID 引用的是产品表中存在的有效产品 ID ，维护了数据的引用完整性。如果订单表中产品 ID 字段的数据类型与产品表不一致，外键约束就无法正确建立，数据的一致性就难以保证。

再比如，在一个学生成绩管理系统中，学生表中的学生 ID 字段作为主键，数据类型为int ，同时具有主键约束，确保每个学生的 ID 唯一且不能为空。成绩表中的学生 ID 字段作为外键，数据类型也为int ，并通过外键约束关联到学生表的学生 ID 字段。这样，当插入成绩记录时，就能保证成绩记录对应的学生 ID 是存在于学生表中的有效 ID ，防止出现无效的学生 ID 被关联到成绩表的情况，从而保障了数据的完整性。

实际案例演练

假设我们要创建一个员工表，用于存储公司员工的相关信息。这个表需要包含员工编号、姓名、性别、年龄、入职日期、工资、部门编号等字段。下面是创建这个员工表并合理使用数据类型和约束的示例：

-- 创建部门表

CREATE TABLE Department (

DepartmentID int IDENTITY(1,1) PRIMARY KEY,

DepartmentName nvarchar(50) NOT NULL

);

-- 创建员工表

CREATE TABLE Employee (

EmployeeID int IDENTITY(1,1) PRIMARY KEY, -- 员工编号，自增长且为主键，保证唯一性和标识性

EmployeeName nvarchar(50) NOT NULL, -- 员工姓名，不能为空

Gender char(2) CHECK (Gender IN ('男', '女')), -- 性别，只能是男或女，通过检查约束保证

Age int CHECK (Age >= 18 AND Age <= 60), -- 年龄，通过检查约束保证在合理范围

HireDate date DEFAULT GETDATE(), -- 入职日期，默认值为当前日期

Salary decimal(10, 2) CHECK (Salary > 0), -- 工资，使用decimal类型保证精确性，并通过检查约束保证大于0

DepartmentID int, -- 部门编号

FOREIGN KEY (DepartmentID) REFERENCES Department(DepartmentID) -- 外键约束，关联部门表的部门编号

);

在这个例子中，EmployeeID作为主键，确保每个员工有唯一的标识。EmployeeName字段添加了非空约束，保证员工姓名不能为空。Gender字段使用检查约束，确保只能输入 “男” 或 “女” 。Age字段也通过检查约束保证员工年龄在 18 到 60 岁之间。HireDate字段设置了默认值为当前日期，使用date类型只存储日期。Salary字段使用decimal(10, 2)类型，精确到小数点后两位，用于存储工资，并通过检查约束保证工资大于 0 。DepartmentID作为外键，关联Department表中的DepartmentID，建立员工与部门之间的关系，保证数据的引用完整性。

通过这个实际案例可以看出，合理选择数据类型和添加适当的约束，能够有效地保证数据库中数据的准确性、完整性和一致性，为后续的业务操作和数据分析提供可靠的数据基础。

总结与展望

在 SQL Server 的领域中，数据类型与约束是构建强大、可靠数据库的基础。通过深入了解系统数据类型，我们能够根据数据的特性和业务需求，精确地选择合适的数据类型，这不仅保证了数据存储的高效性，还为数据的准确性提供了保障。用户自定义数据类型则进一步增强了数据库设计的灵活性和可维护性，使我们能够更好地贴合业务逻辑。

而约束，作为数据完整性的守护者，从主键约束确保每一行数据的唯一性，到外键约束维护表间的关联关系；从唯一约束保证特定列值的独特性，到检查约束验证数据的合理性；从默认约束提供便捷的数据插入方式，到非空约束杜绝数据的缺失，每一种约束都在各自的岗位上发挥着重要作用，共同构建了一个严谨的数据环境。

数据类型与约束的协同作用更是为数据库的稳定运行保驾护航，它们相互配合，从不同层面保障数据的质量，避免无效数据的产生，确保数据的一致性和可靠性。在实际案例中，我们看到了如何通过合理运用数据类型和约束，创建出符合业务需求的数据库表，为企业的运营和决策提供坚实的数据支持。

对于读者而言，掌握 SQL Server 的数据类型与约束知识，是踏入数据库管理领域的重要一步。在未来的学习和工作中，随着业务场景的不断变化和数据量的持续增长，我们还将面临更多关于数据管理和优化的挑战。希望大家能够继续深入学习数据库相关知识，不断提升自己的数据处理能力，在数据库管理的道路上不断探索前行，充分发挥 SQL Server 的强大功能，为企业的数据资产保驾护航。