约束的定义:
约束是一种限制,作用于列级别或者行级别,确保数据符合设计要求和满足数据完整性规则。
约束的分类:
1.域约束:作用于列上的约束,比如CHECK和DEFAULT。
2.实体约束:作用于行上的约束,比如:PRIMARY和UNIQUE。
3.引用完整性约束:当某列必须与其他列有依赖关系时,FOREIGN KEY。
约束的种类:
主键(PRIMARY KEY):行的唯一标识符
外键(FOREIGN KEY):列依赖于其他列
UNIQUE约束:列(或者列组合)的值必须唯一
CHECK约束:设置规则来限制列值
DEFAULT约束:默认值
-----------------------------------------------------------------------------------------------------------------------
创建四个表,用于学习约束
表1:供货商表(供货商ID,供货商名称)
CREATE TABLE [dbo].[suppliers]( SupplierID int NOT NULL PRIMARY KEY IDENTITY(1000,1), SupplierName varchar(20) NOT NULL)
表2:商品表(商品ID,商品名,供货商ID)
CREATE TABLE [dbo].[goods]( GoodsID int NOT NULL PRIMARY KEY, GoodsName varchar(20) NOT NULL, SupplierID int NOT NULL FOREIGN KEY REFERENCES suppliers(SupplierID))
表3:库存表(主键ID,商品ID,商品数量)
CREATE TABLE [dbo].[purchase]( PurchaseID int NOT NULL PRIMARY KEY IDENTITY(100,1), GoodsID int NOT NULL FOREIGN KEY REFERENCES goods(GoodsID), GoodsTotal int NOT NULL, PurchaseDate smalldatetime NOT NULL DEFAULT(GETDATE()))
表4:商品分类表(分类ID,分类名,父分类ID,分类深度)
CREATE TABLE [dbo].[GoodsSort]( SortID int NOT NULL PRIMARY KEY IDENTITY(1000,1), SortName varchar(20) NOT NULL, ParentID int NOT NULL, depth smallint NOT null)
约束1:主键约束
约束语法:列名 PRIMARY KEY
1.创建表时设置主键,如表1创建范烈。注:创建表建立的主键名是默认的,由系统随机命名。
2.在现有表上设置主键:
格式: ALTER TABLE 表名 ADD CONSTRAINT 主键名 PRIMARY KEY (列名)
ALTER TABLE [dbo].[suppliers] ADD CONSTRAINT PK_SupplierID PRIMARY KEY (SupplierID)
约束2:外键约束
建立列(依赖列)与列(被依赖列)之间的依赖。依赖列的值,在被依赖列上都应有匹配的值。
注意:被依赖列,必须为主键列或者UNIQUE列,具体来说就是该列不能有重复值,我看的那个该死的教程上根本没讲。
知识点1:创建表时设置外键约束,如表2创建范例。
知识点2:在现有表上设置外键:
格式:ALTER TABLE 依赖表名 ADD CONSTRAINT 外键名 FOREIGN KEY (依赖列名)REFERENCES 被依赖表名(被依赖列名)
ALTER TABLE [dbo].[goods] ADD CONSTRAINT FK_SupplierID FOREIGN KEY (SupplierID) REFERENCES suppliers(SupplierID)
知识点3:自引用:依赖列和被依赖列属于同表。
以表4为例进行“自引用”
第一步:由于是自引用,必须先有一条记录,然后添加“自引用”外键
INSERT [dbo].[GoodsSort](SortName,ParentID,depth) VALUES('根类',0,1)
第二步:设置外键
ALTER TABLE [dbo].[GoodsSort] WITH NOCHECK ADD CONSTRAINT FK_ParentID FOREIGN KEY (ParentID) REFERENCES GoodsSort(SortID)
注:这里加了关键词“WITH NOCHECK”,因为之前添加的一条记录不满足先添加的外键规则。使用关键词“WITH NOCHECK”,告诉系统设置外键的时候不检查之前的记录是否符合外键规则。
第三步:
如果我们插入:INSERT [dbo].[GoodsSort](SortName,ParentID,depth) VALUES('C语言',1000,2),插入成功。
如果我们插入:INSERT [dbo].[GoodsSort](SortName,ParentID,depth) VALUES('C++语言',1005,2),则插入失败。
因为ParentID=1005这个值是不存在的。
知识点4: 级联操作(我直接理解成依赖操作)即被依赖的列进行修改或者删除的时候,依赖列应该做什么相应的反应。
语法:ON UPDATE {NO ACTION|CASCADE|SET NULL|SET DEFAULT}
ON DELETE {NO ACTION|CASCADE|SET NULL|SET DEFAULT}
默认:NO ACTION,不允许更新或者删除。
对表1插入数据(被依赖列必须先写入数据)
INSERT [dbo].[suppliers](SupplierName) Values('清华出版社')
INSERT [dbo].[suppliers](SupplierName) Values('武汉出版社')
INSERT [dbo].[suppliers](SupplierName) Values('希望出版社')
得到表值,如下:
SupplierID | SupplierName |
1000 | 清华出版社 |
1001 | 武汉出版社 |
1002 | 希望出版社 |
对表2插入记录:
--语句1:可正常插入
INSERT[dbo].[goods](GoodsName,SupplierID) VALUES('C语言基础第一版',1000);
--语句2:可以正常插入INSERT [dbo].[goods](GoodsName,SupplierID) VALUES('深入C语言',1002);
--语句3:系统会报错。表suppliers的列SupplierID并含有“1005”这个值INSERT [dbo].[goods](GoodsName,SupplierID) VALUES('C语言算法',1005);
尝试删除表suppliers的记录
DELETE FROM [dbo].[suppliers] WHERE SupplierID=1000;
语句报错,因为表suppliers是被依赖表,默认是DELETE行为是NO ACTION
修改外键的级联处理方式
--删除外键 FK_SupplierIDALTER TABLE [dbo].[goods] DROP CONSTRAINT FK_SupplierID--重新创建FK_SupplierID,设置UPDATE和DELETE的级联处理方式ALTER TABLE [dbo].[goods] ADD CONSTRAINT FK_SupplierID FOREIGN KEY (SupplierID) REFERENCES suppliers(SupplierID) ON UPDATE CASCADE
再次运行:DELETE FROM [dbo].[suppliers] WHERE SupplierID=1000,删除成功。
同时级联删除表[goods]对应的记录。
关于“外键约束”的两个问题:
1.如果一个表同时设置两个外键,则只能对一个外键设置CASCADE。
2.如果一个表设置了两个外键,其中有一个设置CASCADE级联操作,则对另外一个外键做DELETE处理的时候同样成功。
这两个问题有些想不明白。