源栈课堂

源栈课堂 · 一起帮

学编程，来源栈；先学习，再交钱！
约束：非空 / UNIQUE / PRIMARY / CHECK / DEFAULT，自动编号：自增 / UUID
当前系列： SQL 修改讲义

约束

除了数据类型的限制，SQL Server还为我们提供了其他一些约束（constraint），包括：

  1. 非空约束（NOT NULL）：该列数据必须有值，不能为空。
  2. 默认值（DEFAULT）：如果INSERT的时候没有填写，给一个默认值
  3. 自定义约束（CHECK）： 比如数值必须大于0
  4. 唯一约束（UNIQUE）：该列数据是不能重复的，该列每一行的数据都必须是唯一的。
  5. 主键约束（PRIMARY KEY）：同时保证唯一和非空。且一个表只能有一个主键。
  6. 外键约束（FOREIGN KEY）：后文详述

所有上面这些约束，都可以在

建表时指定 

CREATE TABLE Student( 
    Id INT PRIMARY KEY, -- Id是主键，不能为NULL不能重复 
    SName VARCHAR(10) UNIQUE, -- SName可以为空不能重复 
    Enroll DATETIME  NOT NULL, -- Enroll不能为空 
    Age INT CHECK(Age>18), -- Age必须大于18 
    IsFemale BIT DEFAULT(1), -- 默认为1 
    Score FLOAT -- 没有约束 
)

演示：当表上已有约束，我们进行操作（增删改）时不能违反 

INSERT Student(Id, SName, Enroll, Age, IsFemale, Score)
VALUES(1, 'atai', '2022-11-14', 19, 0, 85);
注意：特殊的NULL值 
-- NULL值和非NULL值不违反UNIQUE约束
INSERT Student(Id, Enroll, Age, IsFemale, Score)
VALUES(3, '2022-11-14', 19, 0, 85);
-- NULL和NULL被认为是重复的
INSERT Student(Id, Enroll, Age, IsFemale, Score)
VALUES(4, '2022-11-14', 19, 0, 85);

查看工具生成的其他写法（先列后约束）：

T-SQL mysql 
CREATE TABLE [dbo].[Student] (
    [Id]       INT          NOT NULL,
    [SName]    VARCHAR (10) NULL,
    [Enroll]   DATETIME     NOT NULL,
    [Age]      INT     NULL,
    [IsFemale] BIT          DEFAULT ((1)) NULL,
    [Score]    FLOAT (53)   NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC),
    UNIQUE NONCLUSTERED ([SName] ASC),
    CHECK ([Age]>(18))
);
 
CREATE TABLE `student` (
  `Id` int NOT NULL,
  `SName` varchar(10) DEFAULT NULL,
  `Enroll` datetime NOT NULL,
  `Age` INT DEFAULT NULL,
  `IsFemale` bit(1) DEFAULT (1),
  `Score` float DEFAULT NULL,
  PRIMARY KEY (`Id`),
  UNIQUE KEY `SName` (`SName`),
  CONSTRAINT `student_chk_1` CHECK ((`Age` > 0))
) ENGINE=InnoDB...
右键：view code 右键：Table-Inspector-DLL

PRIMARY KEY和UNIQUE约束，都可以建在多列上，这时候就只能先列后约束的写法： 

PRIMARY KEY ([Name], Age)
UNIQUE ([Name], Age)

意思是某行中的Name和Age不能和其他某一行的Name和Age都相同（只有一个相同是可以的）

如果是在表上添加新列，可以像建表时一样，指定该列的一些约束，比如： 

ALTER TABLE Student 
ADD Hobby VARCHAR(100) DEFAULT('coding');

更改

表上已有列的约束，按是否独立，可分为两种方式：

  1. 没有独立的约束，同修改列（复习）
  2. 约束是独立的，使用ADD/DROP的方法添加/删除

除了非空约束，T-SQL和mysql使用相同方式。


T-SQL mysql
同：修改列（复习） 非空 非空和默认
ADD/DROP CONSTRAINT 其他
 其他

注意：

  1. 使用ALTER切换NULL和NOT NULL时，一样要带着该列的类型。
  2. 有独立约束的列不能直接删除，因为约束依赖于列。

双向奔赴

当表中已有数据，我们也不能建立和数据不符的约束。

比如，不能：

  • 在一个已有NULL值的列上添加NOT NULL约束
  • 在一个已有重复值的列上建立UNIQUE约束

注意这里面有一个常见的错误：添加一个没有DEFAULT且要求NOT NULL的新列！@想一想@：为什么？

因为添加新列后新列值必然为NULL，会和要求的NOT NULL约束冲突。 

ALTER TABLE Student
ADD Hobby VARCHAR(500) NOT NULL   -- 报错
-- DEFAULT('coding')  -- OK
;

PS：外键约束还会导致列无法被删除等……

约束修改：T-SQL

NOT NULL以外的约束都是独立的，只能先DROP，再ADD。 

CREATE TABLE Student(
    Id INT , 
    SName VARCHAR(10) , 
    Enroll DATETIME,
    Age INT , 
    IsFemale BIT, 
    Score FLOAT
);

约束名称

不指定约束的名称，系统会自动生成约束名，通常格式为：[约束种类]_[表名]_[随机字符串]，如UQ_Student_737584F6d87643。

所以T-SQL中是可以指定约束名称的： 

CREATE TABLE Student( 
    Id INT CONSTRAINT PK_Student_Id PRIMARY KEY,
    SName VARCHAR(10) CONSTRAINT UQ_Student_SName UNIQUE, 
    Enroll DATETIME  NOT NULL,
    Age INT CONSTRAINT CK_Student_Age CHECK(Age>18), 
    IsFemale BIT CONSTRAINT FK_Student_IsFemale DEFAULT(1), 
    Score FLOAT 
);

注意：约束中加上表名，是因为约束是否重名是以数据库为单位进行检查的。（演示：重名约束）

这样在Explorer中可以查看生成的约束：

  • 主键和唯一约束放置在Keys中，飞哥的理解是因为他们依赖于整列的数据
  • 默认和自定义约束放置在Constraints中，因为他们可独立完成
  • NOT NULL约束没有呈现在这里

ADD

在ALTER TABLE之后使用ADD CONSTRAINT ：

  • PRIMARY KEY约束： 
     -- 在Id列上建立主键约束
ALTER TABLE Student 
ADD CONSTRAINT PK_Student_Id PRIMARY KEY(Id);
  • UNIQUE约束： 
    -- 在Enroll列上添加唯一约束
ALTER TABLE Student
ADD CONSTRAINT UQ_Student_ENROLL UNIQUE(ENROLL);
  • CHECK约束： 
    -- CK_Age是添加约束的名称
ALTER TABLE Student
ADD CONSTRAINT CK_Student_Age CHECK(Age>0);
  • DEFAULT约束：
    -- 18是默认值,作用于Age列
ALTER TABLE Student
ADD CONSTRAINT DF_Student_Age DEFAULT 18 FOR Age; -- 注意写法的差异

上述代码使用CONSTRAINT关键字指定了自定义的约束名称，便于我们删除。

DROP

在ALTER TABLE之后使用DROP CONSTRAINT ： 

ALTER TABLE Student
DROP CONSTRAINT PK_Student_Id;

约束修改：mysql

使用ALTER TABLE修改DEFAULT： 

ALTER TABLE student
modify IsFemale bit default(1);

自定义约束CEECK： 

ALTER TABLE student
-- MODIFY Age INT CHECK(Age>18);   不报错，但也不生效
ADD CONSTRAINT CK_Age CHECK(Age>18);
-- DROP CONSTRAINT CK_Age;

唯一约束： 

ALTER TABLE student
-- ADD CONSTRAINT UQ_Student_SName UNIQUE(SName);
DROP CONSTRAINT UQ_Student_SName;
主键约束： 
ALTER TABLE student
-- ADD CONSTRAINT PK_Student_Id PRIMARY KEY(Id); 
DROP PRIMARY KEY;  -- 不需要名字


概念辨析

主键 vs 主键约束

我们通常也将“主键”和“主键约束”混用。

但严格来说，“主键”和“主键约束”并不是同一个概念：

  • 主键，是指能够唯一标识一行数据的一列或多列；
  • 主键约束，是一种非空且唯一的约束

我们说：

  • “这一列是主键”，意思是“这一列被作为主键被使用”。
  • “将这一列设置成主键”，
    • 本来，也应该只是“将这一列当做主键来使用”的意思，并不一定就是要添加主键约束；
    • 但是，在主键上设置主键约束是如此的理所当然，所以就是让你添加主键约束的意思。

同学们要根据上下文综合理解（外键也一样）。

主键 vs 唯一

一张表只能有一个主键（列/约束），但可以有多个唯一（列/约束）。

唯一列可以做主键，但不一定是主键，有时候我们又将其称之为“候选”（码）。

唯一（列/约束）不强调其作用（标识），只强调其特征（不重复）。


自动编号

@想一想@：应该选择哪一列做主键？

主键通常使用

  1. 数据库自动生成的
  2. 总是不为NULL且唯一的

自动编号（惯用名Id）。

相较于表中已有的、可以区分行数据的列（比如姓名/学号/身份证号，又被称之为“业务”主键）。这种“逻辑”主键可以：

  • 让系统更有弹性（flexible），应对以后可能的更改
  • 因为自动化，可以确保不会因为用户输入产生重复或错误

PS：通俗版理解：

  1. 身份证号和人本身的属性无关（是“逻辑”的）
  2. 为什么身份证号比“姓名+出生地+生日”更好？
  3. 自动的生成身份证好，是不是比手工计算生成更好？

T-SQL为我们提供了两种自动编号：

自增列

整数类型，需要在建表时声明该列为自增。

T-SQL mysql 
CREATE TABLE Student( 
	-- 关键字IDENTITY
	Id INT IDENTITY,  
	SName VARCHAR(50)
 
CREATE TABLE Student( 
        -- 关键字auto_increment
        Id INT auto_increment primary key,
	SName VARCHAR(50)
没有这个限制 必须同时为主键
在INSERT插入数据的时候，自增列不能再由开发人员赋值。 
-- 错误：试图把Id设成20
INSERT INTO Student(Id, SName) VALUES (20, 'atai') 
-- 正确：Id由数据库自动生成
INSERT INTO Student(SName) VALUES ('atai')
没有这个限制

注意：一个表中只能有一个自增列。

每插入一条新数据，就在上一条数据编号的基础上自增（默认从1开始，每次+1）作为新数据的编号。

演示：插入数据后自动生成自增列值

注意：自增列的值可以不连续，因为：

  • 即使没有成功插入，自增列值也会增加
  • Id一旦生成，不能更改；即：删除一条数据，自增列值也不会被“压缩”
  • 为保证自增列不重复，数据库会在某些情况（如session中断）自动大幅增加值
  • ……

GUID或UUID

Global/Universal Unique Id。全球/宇宙唯一Id，^_^

字符串类型。建表时无须其他指定： 

CREATE TABLE GUIDSample(Id VARCHAR(50) )
在插入数据时调用函数

T-SQL mysql
NEWID() uuid()

该值根据当前时间和网卡的MAC地址等，由数据库自动生成。

如何选择

#常见面试题#

和GUID想比较，自增列的：

  • 优势是：
    • 直观，
    • 性能更高（整数累加比生成一个GUID快太多）
  • 劣势是：
    • 必须在INSERT之后（通过全局变量@@IDENTITY）才能得到新插入行的Id
    • 要小心集群冲突：


【选】起始值和步长

通常当我们使用数据库集群（可简单理解为同一张表分别放在多个数据库中）的时候，比如3台数据库A,B,C：

如果还是以单个数据库为域自增，就必然出现冲突：

  • A数据库上表的ID是1,2,3,4...
  • B数据库上表的ID还是1,2,3,...
  • C数据库上表的ID也是1,2,3,4,5...

用SELECT * FROM T WHERE Id = 3 会取出3行数据出来，完全不对劲！

如果要检查所有数据库找到当前最大值（或者检查某个地方的@IDENTITY）之后再自增，效率就会降低。

有一种解决方案就是这样设置他们表上的ID列：

  • A：从1开始，每次自增3个，于是A数据库上的ID只能是1,4,7,10...
  • B：从2开始，每次自增3个，于是B数据库上的ID只能是2,5,8...
  • C：从3开始，每次自增3个，于是C数据库上的ID只能是3,6,9,12,15...

他们的ID是不是就可以依赖自己（当前数据库）保证ID永远不会重复了？

这实际上就是设置数据库的

  • 起始值：从start开始
  • 步增/长：每次增加step

比如：从10开始，每次增加3

T-SQL

mysql 

IDENTITY(10,3)
 
CREATE TABLE student (
  Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
  SName varchar(50)
) AUTO_INCREMENT=10;
SET @@auto_increment_offset=3;
建表时指定
作用于该表 		起始值针对表
步长为全局变量，作用于当前连接


作业

  1. 在User表上的基础上：
    1. 添加Id列（INT类型，以后不特别说明，Id都是INT类型），让Id成为主键
    2. 添加约束，让UserName不能重复
  2. 在Problem表的基础上：
    1. 为NeedRemoteHelp添加NOT NULL约束，再删除NeedRemoteHelp上NOT NULL的约束
    2. 添加自定义约束，让Reward不能小于10
  3. 观察一起帮的“关键字”功能，新建Keyword表，要求带一个自增的主键Id（【选】起始值为10，步长为5）；并存入若干条数据
  4. 将User表中Id列修改为可存储GUID的类型，并存入若干条包含GUID值的数据
  5. Problem表已有Id列，试一试，能否将该列改成自增？

学习笔记
源栈学历
键盘敲烂，月薪过万；作业不做，等于没学！

作业

  1. 约束：
    1. 在User表上的基础上：
      1. 添加Id列（INT类型，以后不特别说明，Id都是INT类型），让Id成为主键
      2. 添加约束，让UserName不能重复
    2. 在Problem表的基础上：
      1. 为NeedRemoteHelp添加NOT NULL约束，再删除NeedRemoteHelp上NOT NULL的约束
      2. 添加自定义约束，让Reward不能小于10
    3. 观察一起帮的“关键字”功能，新建Keyword表，要求带一个自增的主键Id（【选】起始值为10，步长为5）；并存入若干条数据
    4. 将User表中Id列修改为可存储GUID的类型，并存入若干条包含GUID值的数据
    5. Problem表已有Id列，试一试，能否将该列改成自增？
觉得很 ，不要忘记分享哟！

任何问题，都可以直接加 QQ群：273534701

在当前系列 SQL 中继续学习：

多快好省！前端后端，线上线下，名师精讲

  • 先学习，后付费；
  • 不满意，不要钱。
  • 编程培训班，我就选源栈

更多了解 加：

QQ群：273534701

答疑解惑，远程debug……

B站 源栈-小九 的直播间

写代码要保持微笑 (๑•̀ㅂ•́)و✧

公众号：源栈一起帮

二维码