Microsoft SQL Server学习（四）--约束

SQLServer - 约束

主要是为了保证数据库中的数据一致性、有效性、准确性，
从而提高了数据库中数据的正确性

一、约束的分类

在SQLserver中，约束分三种不同类型

• 1.实体约束
  实提约束是关于行的。比如某一行的值就不允许出现在其他行，比如主键
• 2.域约束
  域约束是关于列的。对于所有行，某一列是有那些约束，比如check约束
• 3.参照完整性约束
  如果某列的值必须与其他列匹配，就需要一个参照完整性约束，比如外键

---

二、约束命名

SQLServer在我们不提供名称时，会自动创建名称，但是由系统自动创建的名称并不是特别有用。
为了能够一眼看上去就知道这个约束是用来干什么的，我们应该使用一种简单明了的短语来进行命名。
例如要确保某一列电话号码格式正确的约束，我们可以使用命名CK_Customers_PhoneNo这样的短语来命名。

---

三、键约束

• 主键约束（Primary Key constraint）：要求主键列数据唯一，并且不允许为空。
• 唯一约束（Unique constraint）：要求该列唯一，允许为空，但只能出现一个空值。
• 检查约束（Check constraint）：某列取值范围限制，格式限制等，如有关年龄、邮箱（必须有@）的约束。
• 默认约束（Default constraint）：某列的默认值，如在数据库里有一项数据很多重复，可以设为默认值。
• 外键约束（Foreign Key constraint）：用于在两个表之间建立关系，需要指定引用主表的哪一列。
• *不为空约束（Not Null）

添加语法：

    ALTER TABLE 表名
    ADD CONSTRAINT 约束名 约束 类型 具体的约束说明
    上述语法表示修改某个表，添加某个约束。其中，约束名的命名规则推荐采用“约束类型_约束字段”这样的形式。

---

示例：

添加主键约束（将stuNo设为主键）：
ALTER TABLE stuInfo
ADD CONSTRAINT PK_stuNo PRIMARY KEY (stuNo)

添加唯一约束（身份证号唯一）：
ALTER TABLE stuInfo
ADD CONSTRAINT UQ_stuID UNIQUE (stuID)

添加默认约束（如果地址不填，默认为“地址不详”）：
ALTER TABLE stuInfo
ADD CONSTRAINT DF_stuAddress DEFAULT ('地址不详') FOR stuAddress

添加检查约束（要求年龄只能在 15~40岁之间）
ALTER TABLE stuInfo
ADD CONSTRAINT CK_stuAge CHECK(stuAge BETWEEN 15 AND 40)

添加外键约束（主表 stuInfo 和从表 stuGrade 建立关系，关联字段为 stuNo ）：
ALTER TABLE stuGrade
ADD CONSTRAINT FK_stuNo
FOREIGN KEY (stuNo) REFERENCES stuInfo(stuNo)
GO

---

删除语法：

ALTER TABLE 表名
DROP CONSTRAINT 约束名

示例：

删除stuInfo表中地址默认约束的语句：
ALTER TABLE stuInfo
DROP CONSTRAINT DF_stuAddress

删除外键约束注意事项：

对于存在外键约束的表，如果进行删除非空的外键，可能会出现错误。

• 如果在 FOREIGN KEY 约束的列中输入非 NULL 值，则此值必须在被引用的列中存在，否则将返回违反外键约束的错误信息。
• FOREIGN KEY 约束应用于前面所讲的列，除非指定了源列。
• FOREIGN KEY 约束仅能引用位于同一服务器上的同一数据库中的表。数据库间的引用完整性必须通过触发器实现。有关更多信息，请参见 CREATE TRIGGER。
• FOREIGN KEY 可以引用同一表中的其它列（自引用）。
• 列级 FOREIGN KEY 约束的 REFERENCES 子句仅能列出一个引用列，且该列必须与定义约束的列具有相同的数据类型。
• 表级 FOREIGN KEY 约束的 REFERENCES 子句中引用列的数目必须与约束列列表中的列数相同。每个引用列的数据类型也必须与列表中相应列的数据类型相同。
• 如果 timestamp 类型的列是外键或被引用键的一部分，则不能指定CASCADE。
• 可以在相互间具有引用关系的表上组合使用 CASCADE 和 NO ACTION。如果 SQL Server 遇到 NO ACTION，将终止执行语句并回滚相关的 CASCADE 操作。当 DELETE 语句导致 CASCADE 和 NO ACTION 组合操作时，在 SQL Server 检查 NO ACTION 操作之前将执行所有 CASCADE 操作。
• 一个表最多可包含 253 个 FOREIGN KEY 约束。
• 对于临时表不强制 FOREIGN KEY 约束。
• 每个表在其 FOREIGN KEY 约束中最多可以引用 253 个不同的表。
• FOREIGN KEY 约束只能引用被引用表的 PRIMARY KEY 或 UNIQUE 约束中的列或被引用表上 UNIQUE INDEX 中的列。

---

--创建不为空约束的方法一
alter table class_A
alter column name varchar(50) not null

--创建不为空约束的方法二
create table test_520shq(
id int,
manager_name varchar(50) not null,
phoneNo int
)

验证约束: insert into test_520shq values(1,null,'552222')
-----------------------------------------------------------

drop table test_520shq_D

select * from test_520shq

create table test_520shq_A(
id int,
java_name varchar(50),
phoneNo int
)

--创建主键约束方法一
alter table test_520shq_A
alter column id int not null 
alter table test_520shq_A
add constraint pk_test520shqID primary key(id)

--创建主键约束方法二
create table test_520shq_B(
id int primary key,
net_name varchar(50),
phoneNo int
)

--创建主键约束方法三
create table test_520shq_C(
id int,
net_name varchar(50),
phoneNo int,
constraint pk_id primary key(id)
)

--查询约束
exec sp_helpconstraint test_520shq_C

--删除约束
alter table test_520shq_C
drop constraint pk_id


--检查约束方法一
create table test_520shq_D(
id int primary key,
name varchar(50),
phone_No bigint check (phone_No>9999999999 and phone_No<100000000000)
)

验证约束: insert into test_520shq_D values (1,'tom',138877905089)

--检查约束方法二
create table test_520shq_E(
id int primary key,
name varchar(50),
phone_No bigint
)

alter table test_520shq_E
add constraint ck_phoneNo check(phone_No>9999999999 and phone_No<100000000000)

execute sp_helpconstraint test_520shq_E

alter table test_520shq_E 
drop constraint ck_phoneNo

drop table test_520shq_E

--检查约束方法三
create table test_520shq_E(
id int primary key,
name varchar(50),
phone_No bigint,
constraint ck_phone_No check(phone_No>9999999999 and phone_No<100000000000)
)

--创建唯一约束方法一
create table test_520shq_F(
logo_name varchar(50) unique(logo_name)
)

验证约束:insert into test_520shq_F values (null)
--检查约束方法二
create table test_520shq_E(
id int primary key,
name varchar(50),
phone_No bigint
)

alter table test_520shq_E
add constraint ck_phoneNo check(phone_No>9999999999 and phone_No<100000000000)

execute sp_helpconstraint test_520shq_E

alter table test_520shq_E 
drop constraint ck_phoneNo

drop table test_520shq_E

--检查约束方法三
create table test_520shq_E(
id int primary key,
name varchar(50),
phone_No bigint,
constraint ck_phone_No check(phone_No>9999999999 and phone_No<100000000000)
)

--创建唯一约束方法一
create table test_520shq_F(
logo_name varchar(50) unique(logo_name)
)

验证约束:insert into test_520shq_F values (null)

--创建唯一约束方法二
alter table goods
add constraint uq_goodsprice unique(goods_price)

--创建唯一约束方法三
create table student3(
student3_age int,
student3_name varchar(10),
constraint uq_student3_age unique(student3_age)

--默认约束
--第一种方法
create table student4(
student_sex char(4) default '男'
)
验证: insert into student3 values (default)
--第二种方法
alter table goodschart
add constraint de_goodsprice default(500) for goodsprice

--外键约束
--在创建外键时必须分析出参照表和被参照表
create table student10(
student_id int,
student_name varchar (10),
student_age int
)
insert into student10 values(3,'张三',12)
create table score(
student_id int,
score int
)

/*
外键加在参照表上
在创建外键时，必须要求被参照表中的被参照字段设置主键
被参照表中的student_id必须添加主键
添加外键语法:
方法一:不为空约束加上主键约束加上如下约束
alter table参照表名
add constraint 约束名 foreign key（参照表的字段名）references 被参照表
注意：当使用该种方法进行添加进行添加外键时，
必须要求被参照表中的参照字段设置主键
*/

alter table student10
alter column student_id int not null
alter table student10
add constraint pk_studentid1 primary key(student10_id)
alter table score
add constraint fk_studentid foreign key(student_id)references student10

/*
方法2:
creat table 参照表名
（
字段名（被参照表得） foreign key references 被参照表名
）
方法3：
create table 参照表名
（
字段名
Constraint fk_约束名 foreign key(字段名)
）
*/

===========================================