大多数人,都低估了编程学习的难度,而高估了自己的学习能力和毅力。
当前系列: 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站 源栈-小九 的直播间

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

公众号:源栈一起帮

二维码