完整性与约束

完整性

实体完整性：

定义：

指关系中的主属性值不能为Null且不能有相同值

保证行与行之间不同

约束方法：

主键约束、唯一约束、标识列。

 域完整性

定义：

保证列的完整性、合法。

约束方法：

数据类型、默认值、缺省值、检查约束、非空约束

限制数据类型,缺省值,规则,约束,是否可以为空,域完整性可以确保不会输入无效的值.。

引用完整性：

定义：

引用的数据必须在源数据中。

约束方法：

外键约束

自定义完整性：

定义：

根据业务处理流程定义的特定业务规则

约束方法：

触发器、存储过程、规则。

 

 

约束

部分参考：https://www.cnblogs.com/shuzhenyu/p/5725412.html

目的：

保证数据完整性。

非空约束（NOT NULL）：

要求：

该列不为NULL值。

IF OBJECT_ID('dbo.Orders','U') IS NOT NULL
DROP TABLE dbo.Orders;

Create TABLE dbo.Orders
(

orderid INT CONSTRAINT PK_Orders PRIMARY KEY,  --PK_Orders:主键名称
NAME_Q  CHAR(50) CONSTRAINT NULL_Orders NOT NULL  --    NULL_Orders:非空约束名  

)

 

 

主键约束（PRIMARY KEY）：

要求：

1.数据唯一，不许为空。

2.可以由多个字段组成，但最好为单个字段。

3.数值最好没有意义，比如ID号。

4.创建表时创建主键：

Create TABLE dbo.Orders
(
orderid INT CONSTRAINT PK_Orders PRIMARY KEY,  --PK_Orders:主键名称
)


Create TABLE dbo.Orders
(
orderid INT NOT NULL,
CONSTRAINT PK_Orders                          --建立表时，直接创建主键约束
PRIMARY KEY (orderid)
);

5.表创建之后修改主键：

ALTER TABLE dbo.Order
ADD CONSTRAINT PK_Order
PRIMARY KEY(orderid)

 

外键约束（FOREIGN KEY）：

参考：https://www.cnblogs.com/chenxiaohei/p/6909318.html

要求：

1.如果一个字段X在一张表（表一）中是主关键字，而在另外一张表（表二）中不是主关键字，则字段X称为表二的外键。

2.如果关系模式R1中的某属性集不是自己的主键，而是关系模式R2的主键，则该属性集称为是关系模式R1的外键。

on delete  规则：

3. CASCADE：级联

   （1）所谓的级联删除，就是删除主键表的同时，外键表同时删除。

   （2）以上面的例子将就是，假如院系表中的某个院系被删除了，那么在学生表中要想查询这个被删除的院系号所对应的院信息就会报错，因为已经不存在这个系了，所以，删除院系表（主键表）时必须删除其他与之关联的表，这里就说明了外键的作用，保持数据的一致性、完整性。当然反过来讲，你删除学生表中的记录，并不影响院系表中的数据，你查询院系号也能正确查询。所以删除外键表中的数据并不影响主键表。

4.NO ACTION(非活动，默认)、RESTRICT：约束/限制
  当取值为No Action或者Restrict时，则当在主键表中删除对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除。（即外键表约束主键表）
5.SET NULL
    当取值为Set Null时，则当在主键表中删除对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为null（，一样是外键表约束主键表，不过这就要求该外键允许取null）。

NO ACTION和RESTRICT的区别：只有在及个别的情况下会导致区别，前者是在其他约束的动作之后执行，后者具有最高的优先权执行。

 

6.创建表时创建外键约束：

CREATE TABLE  qsl  --用户角色表
(
  ID INT PRIMARY KEY IDENTITY(1,1),
  UserID INT NOT NULL,--用户ID
  constraint FK_qsl foreign key(UserID) references qyj(ID)--qyj表的ID作为qsl表的外键
)

 

7.表创建之后修改外键约束：

alter table qsl add constraint FK_qsl  Foreign key (UserID) references  qyj(ID)

 

 

 

 

唯一约束（UNIQUE）：

要求：

1.要求该列唯一，允许为空，但只能出现一个空值。  主键约束是不许为空，只能出现一次。

2.创建表时创建唯一约束：

Create TABLE dbo.Orders
(
orderid INT CONSTRAINT UQ_Orders UNIQUE,  --PK_Orders:唯一约束
)


 Create TABLE dbo.Orders
 (
 orderid INT ,
 CONSTRAINT UQ_Orders                          
UNIQUE (orderid)
)

3.表创建之后修改唯一约束：

ALTER TABLE dbo.Orders
ADD CONSTRAINT UQ_Orders
UNIQUE (orderid)

 

检查约束（CHECK）：

要求：

1.某列取值范围限制、格式限制等，如有关年龄的约束。

2.创建表时创建检查约束：

IF OBJECT_ID('dbo.Orders','U') IS NOT NULL
DROP TABLE dbo.Orders;

Create TABLE dbo.Orders
(
orderid INT CONSTRAINT UQ_Orders UNIQUE,  --PK_Orders:唯一约束
sex char(2)  constraint CK_sex   check(sex in ('男','女'))   --CK_sex:检查约束
)


IF OBJECT_ID('dbo.Orders','U') IS NOT NULL
DROP TABLE dbo.Orders;
 Create TABLE dbo.Orders
 (
 orderid INT ,
 sex char(2) ,
 CONSTRAINT UQ_Orders                          
UNIQUE (orderid),
constraint CK_sex   check(sex in ('男','女'))
)

3.表创建之后修改检查约束：

 alter table new.dbo.Orders  add constraint CK_sex check (sex in ('男','女'))

 

默认值约束（DEFAULT）：

要求：

1.创建表时创建默认值约束：

Create TABLE dbo.Orders
(
orderid INT CONSTRAINT UQ_Orders UNIQUE,  --PK_Orders:唯一约束
sex_1 char(2) constraint DF_sex default '女', --默认值约束
sex char(2)  constraint CK_sex   check(sex in ('男','女'))   --CK_sex:检查约束
)

3.表创建之后修改默认值约束：

ALTER TABLE dbo.Orders ADD CONSTRAINT DF_sex  DEFAULT '女' FOR sex_1

 

标识列：自增（IDENTITY）：

参考：https://blog.csdn.net/tswc_byy/article/details/81747159

 

一、identity的基本用法

1.含义
 identity表示该字段的值会自动更新，不需要我们维护，通常情况下我们不可以直接给identity修饰的字符赋值，否则编译时会报错

2.语法
列名  数据类型  约束  identity(m,n)

m表示的是初始值，n表示的是每次自动增加的值

如果m和n的值都没有指定，默认为（1,1）

要么同时指定m和n的值，要么m和n都不指定，不能只写其中一个值，不然会出错

3.实例演示

不指定m和n的值 

IF OBJECT_ID('student1','U') IS NOT NULL
DROP TABLE student1

create table student1
(
sid int primary key identity,
sname nchar(8) not null,
ssex nchar(1)
)
insert into student1(sname,ssex) values ('张三','男');
insert into student1 values ('李四','女');--可以省略列名
insert into student1 values ('王五','女');

  

指定m和n的值

IF OBJECT_ID('student2','U') IS NOT NULL
DROP TABLE student2
create table student2
(
sid int primary key identity(20,5),
sname nchar(8) not null,
ssex nchar(1)
)
insert into student2(sname,ssex) values ('张三','男');
insert into student2 values ('李四','女');--可以省略列名
insert into student2 values ('王五','女');

select * from student2

 

4.删除一条记录接着插入
把sid为2的记录删除，继续插入，新插入的记录的sid不是2，而是3

create table student3
(
sid int primary key identity,
sname nchar(8) not null,
ssex nchar(1)
)
insert into student3(sname,ssex) values ('张三','男');
insert into student3 values ('李四','女');
delete from student3 where sid=2;--把sid为2的记录删除
insert into student3 values 

 

二、重新设置identity的值
1.语法
dbcc checkident(表名,reseed,n);

n表示的是表中identity字段的初始值

 

2.实例演示

IF OBJECT_ID('student4','U') IS NOT NULL
DROP TABLE student4
create table student4
(
sid int primary key identity,
sname nchar(8) not null,
ssex nchar(1)
)
insert into student4(sname,ssex) values ('张三','男');
insert into student4 values ('李四','女');
delete from student4 where sid=2;--把sid为2的记录删除
dbcc checkident('student4',reseed,1);--把student4表中identity字段的初始值重新设置为1
insert into student4 values ('王五','女');
select * from student4

 

三、向identity字段插入数据
1.语法
set identity_insert 表名 on;
insert into 表名(列名1,列名2,列名3,列名4) values (数据1,数据2,数据3,数据4);
set identity_insert 表名 off;

注意：插入数据时必须得指定identity修饰的字段的名字

2.实例演示

create table student5
(
sid int primary key identity(20,5),
sname nchar(8) not null,
ssex nchar(1)
)
insert into student5(sname,ssex) values ('张三','男');
insert into student5 values ('李四','女');
insert into student5 values ('王五','女');
set identity_insert student5 on;
/*
insert into student5 values ('黑六','男');--error
insert into student5 values (21,'黑六','男');--error
*/
insert into student5(sid,sname,ssex) values (21,'黑六','男');
set identity_insert student5 off;
/*
insert into student5 values (22,'赵七','女');--error
insert into student5(sid,sname,ssex) values (22,'赵七','女');--error
*/
insert into student5 values ('赵七','女');
select * from student5

 

 

 

 

删除约束：

在创建约束时，要给约束命名，否则系统会自动命名。不方便后续操作。

alter table 表名 drop constraint 约束名

 

更改约束：

除非空约束外，其他约束先删后改。