约束的定义:
约束是一种限制,作用于列级或者行级,确保数据符合设计要求和满足数据完整性规则。
约束的分类:
1.域约束:作用于列上的约束,比如CHECK和DEFAULT。
2.实体约束:作用于行上的约束,比如:PRIMARY和UNIQUE。
3.引用完整性约束:当某列必须与其他列有依赖关系时,FOREIGN KEY。
约束的种类:
主键(PRIMARY KEY):行的唯一标识符
外键(FOREIGN KEY):列依赖于其他列
UNIQUE约束:列(或者列组合)的值必须唯一
CHECK约束:设置规则来限制列值
DEFAULT约束:默认值
-----------------------------------------------------------------------------------------------------------------------
创建四个表,用于学习约束
表1:供货商表(供货商ID,供货商名称)
创建示范1:(系统默认指定主键名)
CREATE TABLE dbo.suppliers ( SupplierID int NOT NULL PRIMARY KEY IDENTITY(1000,1), SupplierName varchar(20) NOT NULL )
创建示范2:(指定主键名)
CREATE TABLE dbo.suppliers ( SupplierID int NOT NULL CONSTRAINT PK_SupplierID 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:主键约束
主键用以标识一行,必须是唯一值,一个表只能设置一个主键。
1.创建表时设置主键,如表1的两种创建方式。
2.在现有表上设置主键:
格式: ALTER TABLE 表名 ADD CONSTRAINT 主键名 PRIMARY KEY (列名)
ALTER TABLE dbo.suppliers ADD CONSTRAINT PK_SupplierID PRIMARY KEY (SupplierID)
约束2:外键约束
在列(依赖列)与列(被依赖列)之间的建立依赖。
1.某行“依赖列”的值在“被依赖列”上某行都应有匹配的值
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),则插入失败。
因为1005这个“依赖列”ParentID的值在"被依赖列" SortID 里是不存在的。
知识点4: 级联操作(我直接理解成依赖操作)
A.“依赖列”进行操作时候,“被依赖列”会做相应的反应。
B."被依赖列"进行修改或者删除的时候,"依赖列"会做出相应的反应。
语法: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 | 希望出版社 |
1.“依赖列”插入记录
--语句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);
2.删除“被依赖列”记录
DELETE FROM dbo.suppliers WHERE SupplierID=1000;
语句报错,因为表suppliers是被依赖表,默认是DELETE行为是NO ACTION
修改外键的级联操作方式
--删除外键 FK_SupplierID ALTER 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处理的时候同样成功。
这两个问题有些想不明白。