约束是对表中数据制约的一种手段:
通过约束可以增强表中数据的有效性和完整性
约束可以理解为一种规则或要求,它规定了表中列输入值的范围
分类
SQL Server 数据库主要有以下几大类约束:
除了主键约束在一张表中只允许有一个,其他的约束都可以设置多个。
主键约束:
确保列的唯一性,主键约束的列不能为空。
主键约束可以由多列组成(联合主键)–不用担心行重复问题
默认值约束:
每列只能设置一个默认值约束,
就是即便没有向列中输入信息,也会自动添加一个默认值
(例如淘宝下单时,如果不更改地址,那么就会送到上次的位置)
唯一约束:
与主键约束类似,确保列的唯一性,但是唯一约束可以允许有空值,
可以同时为多列设置唯一约束
检查约束:
规定表中列输入值的取值范围,避免输入一些无用的数据。如商品的价格price>0。
非空约束:
用来规定某列必须输入值
外键约束:
与两张表相关的约束,用于确保两张表中数据的一致性和完整性。
例如在输入学生专业时只能输入学校所开的专业中的一个。
主键约束:
创建表时有两种设置约束的方法:
##除了非空约束和默认值约束必须在列级定义,所有的约束可以选择列级约束和表级约束
列级约束:
在列的定义中直接设置
create table Table_2.1
(
column_name1 datatype [constraint constraint_name] primary key,
column_name2 datatype ,
column_name3 datatype ,
column_name4 datatype ,
……
)--其中constraint_name是为主键约束设置名称的,否则系统自动设置名称
--主键的名称通常会以pk_为前缀
例如:
create table Table_2
(
id int constraint pk_id primary key,
name varchar(20),
price decimal(6,2),
origin varchar(20),
tel varchar(15),
remark varchar(200)
)
表级约束:
在表中列定义设置结束后再定义约束
create table Table_2.2
(
column_name1 datatype ,
column_name2 datatype ,
column_name3 datatype ,
column_name4 datatype ,
……
[constraint constraint_name] primary key(column_name1,column_name2,……)
)--primary key 后面的括号里可以放置一个或多个用于设置主键约束的列,这些列之间用逗号可以隔开
例如:
create table Table_2
(
id int,
name varchar(20),
price decimal(6,2),
origin varchar(20),
tel varchar(15),
remark varchar(200),
constraint pk_id primary key(id)
)
通过视图来查看是否创建成功
select name,type,type_desc
from sys.objects
where parent_object_id=object_id('productinfo') and type='pk';
在修改表时添加主键约束:
ALTER TABLE_name
add [constraint constraint_name] primary key(column_name1,column_name2,……)
例如:
alter table Table_2
add constraint pk_id primary key(id);
删除主键约束:
ALTER TABLE_name
drop constraint constraint_name;
例如:
alter table Table_2
drop constraint pk_id;
外键约束:
列级设置:
create table Table_3
(
column_name1 datatype ,
column_name2 datatype ,
column_name3 datatype ,
column_name4 datatype [constraint constraint_name foreign key(col_name)] references referenced_table_name(ref_col_name)
)
constraint_name:外键约束的名字通常以fk_为前缀
col_name:要设置成外键约束的列名
referenced_table_name:被引用的表名
ref_col_name:被引用的表中的列名
例如:
create table Table_2
(
id int primary key,
name varchar(20),
price decimal(6,2),
origin varchar(20),
tel varchar(15),
remark varchar(200),
supplierid int [constraint fk_product foreign key(supplierid) ]references Table_3(id)
)
表级设置:
create table Table_3
(
column_name1 datatype ,
column_name2 datatype ,
column_name3 datatype ,
column_name4 datatype ,
[constraint constraint_name] foreign key(col_name1,col_name2) references referenced_table_name(ref_col_name1,ref_col_name)
)
例如:
create table Table_4
(
id int primary key,
name varchar(20),
price decimal(6,2),
origin varchar(20),
tel varchar(15),
remark varchar(200),
supplierid int ,
constraint fk_product foreign key(supplierid) references Table_3(id)
)
查看:
select name,type,type_desc
from sys.objects
where parent_object_id=object_id('productinfo') and type='f';
在修改表时设置外键约束:
alter table Table_name
add [constraint constraint_name] foreign key(col_name1,col_name2)] references referenced_table_name(ref_col_name1,ref_col_name)
例如:
alter table Table_2
add constraint fk_product_1 foreign key(supplierid) references Table_3(id);
删除外键约束:(和删除主键约束一样)
alter table Table_2
drop constraint fk_product;
默认值约束
一般添加默认值约束的字段有两种比较常见的情况:
1.该列的值不能为空
2.该列添加的值是一个固定的值
默认值设置只能使用列级设置:
create table Table_3
(
column_name1 datatype ,
column_name2 datatype ,
column_name3 datatype ,
column_name4 datatype [constraint constraint_name] default constant_expression
)
constraint_name:外键约束的名字通常以df_为前缀
default:是默认值约束的关键字
constant_expression:常量表达式,可以是一个具体的值,也可以是通过表达式得到的一个值。
注:每个字段只可以设置一个默认值。
create table productinfo
(
id int primary key,
name varchar(20),
origin varchar(20) default '海南',
tel varchar(20)
)
在修改表时添加默认值约束:
不能改同一行添加两个默认值约束:
alter table table_name
add [constraint constraint_name] default constant_expressiong for col_name;
删除默认值约束
alter table table_name
drop constraint constraint_name;
检查约束
检查约束的作用就是确保在数据表添加的数据是有效的,在添加之前对数据进行检查。
列级设置:
create table Table_name
(
column_name1 datatype ,
column_name2 datatype ,
column_name3 datatype ,
column_name4 datatype [constraint constraint_name] check(expression)
)
表级设置:
create table Table_name
(
column_name1 datatype ,
column_name2 datatype ,
column_name3 datatype ,
column_name4 datatype,
[constraint constraint_name] check(expression)
)
expresson:约束的表达式,允许是一个或多个条件,
例如:
create table Table_4
(
id int primary key,
name varchar(20),
price decimal(6,2) check(price>10),
origin varchar(20),
tel varchar(15),
remark varchar(200)
)
create table Table_4
(
id int primary key,
name varchar(20),
price decimal(6,2) ,
origin varchar(20),
tel varchar(15),
remark varchar(200),
check(price>10)
)
在修改表时添加检查约束:
alter table table_name
add [constraint constraint_name] check(expression);
删除检查约束:
alter table table_name
drop constraint constraint_name;
唯一约束
列级:
create table Table_name
(
column_name1 datatype [constraint constraint_name] unique ,
column_name2 datatype ,
column_name3 datatype ,
column_name4 datatype
)
表级:
create table Table_name
(
column_name1 datatype ,
column_name2 datatype ,
column_name3 datatype ,
column_name4 datatype,
[constraint constraint_name] unique(col_name)
)
在修改表时添加唯一约束
alter table table_name
add [constraint constraint_name] unique(col_name);
删除唯一约束
alter table table_name
drop constraint constraint_name;
非空约束
非空约束通常是在创建数据表时添加,添加非空约束只能在列级设置:
create table Table_name
(
column_name1 datatype not null,
column_name2 datatype ,
column_name3 datatype ,
column_name4 datatype
)
添加非空约束就是在列的数据类型后面添加not null关键字。
在修改表时添加非空约束
alter table table_name
alter column col_name datatype not null;
删除非空约束
alter table table_name
alter column col_name datatype null;