SQLServer第六章：表【约束】

约束是给表设置一些条件，必须满足这些条件才能成立。

语法：

--添加表约束：add constraint 取约束名 增加的约束类型（列名）
--删除表约束：alter table 表名 drop constraint 约束名

--取约束名规则：约束类型大写字母_表名_字段名  ， 如：主键约束名：PK_Info_id

主键约束：

--创建一个表一个字段Test4做演示
create table Info( Test4 int not null )
--添加主键约束：alter table 表格名称 add primary key（列名）
alter table Info add constraint pk primary key(Test4)  --constraint 取约束名，不然数据库会随机取名，因为在SQLserver数据库只能通过约束名删除主键约束。
--删除主键约束：在SQLserver不能使用【alter table 表名 drop primary key】来删除主键，只能用标准的删除约束名删除。不过在MySQL可以使用。
alter table Info drop constraint pk

查询主键约束名：

SELECT
  tab.name AS [表名],
  idx.name AS [主键名称],
  col.name AS [主键列名]
FROM
  sys.indexes idx
    JOIN sys.index_columns idxCol
      ON (idx.object_id = idxCol.object_id
          AND idx.index_id = idxCol.index_id
          AND idx.is_primary_key = 1)
    JOIN sys.tables tab
      ON (idx.object_id = tab.object_id)
    JOIN sys.columns col
      ON (idx.object_id = col.object_id
          AND idxCol.column_id = col.column_id);

外键约束：foreign key

　　外键其实就是引用，因为主键实现了实体的完整性，外键实现了引用的完整性，应用完整性规定，所引用的数据必须存在！说白了就是将两个表关联起来。
　　比如B表存在一个字段b，有外键约束，引用于A表的主键a，那么在向B表插入数据时，字段b必须为A表中a已经存在的值，如过向b中存放一个a中没有的值，则会报违反外键约束。
语法：alter table 表名 add constraint 约束名FK foreign key(列名) references 被引用的表名称（列名）

外键约束注意：

--添加数据：先添加主表数据，在添加从表数据。
--删除数据：先删除从表数据，在删除主表数据。

创建两个表做演示，注意：先执行约束，在添加数据

create database NetBarDB    --创建数据库create database 数据库名
go    --批处理(批量处理)
use NetBarDB                --打开数据库
go

--创建表也是一样有同名的表，需要删除才能创建，可是有点表添加了外键约束无法删除，所以删除表时有时候需要,查询当前表外键约束来源。需要删除外键约束才能删除表。
select FK.name as 外键约束名,FK.object_id,object_name(FK.parent_object_id) as 设置约束的表名 --查询三个字段外键的键名，id ，约束表名：指被哪个表设置了外键约束。
from sys.foreign_keys as FK 
join sys.objects as O on FK.referenced_object_id=O.object_id
where O.name = 'cardInfo'  --cardInfo是取的表名，指需要查询的表名，查被哪些表设置了外键约束，好方便删除外键约束。才能删除表

--exists【存在】 查询到表  用dbo.sysobjects来索引表
if exists(select * from sys.objects where name='Info')
    begin
        --alter table recordInfo drop constraint FK__recordInfo__PCId__573DED66; --这里删除约束是因为存在外链约束，复制约束名过来，删除外键约束才能删除表。
        drop table Info    --删除表：drop table 表名
    end

create table cardInfo
(
    cardId int primary key identity,
    cardPassword varchar(20) not null ,
    min_salary money not null check(min_salary>=100),--money【钱】可做类型 最低薪资 check约束 不小于100元    
    transactTime datetime default getDate() not null,--datetime时间类型,default默认 getdate()当前时间
    stuaddress text --text文本类型
)

create table PCInfo    
(
    PCId int primary key identity,    -- 主键约束：primary key 主键 identity(1,1) 自增不写括号数字默认1,1；意思是1开始后面的每个加1；
    PCUse char(10) not null unique ,   --int整数类型；unique唯一约束：意思是值不能重复。非空约束：not null不为空；加起来相当于主键约束
    pcsex char(10) not null check(pcsex='男' or pcsex='女'),--条件约束check其中，or【或】的意思，and【和】与的意思
    PCNote varchar(50) default '这个是默认值',-- 默认值约束：default 设定默认值；
    PCScore int not null references cardInfo(cardId),--外键，引用cardInfo表,cardId列 
    --primary key (PCId) --设置主键，不太喜欢这种写法，可以直接写字段里。
    --foreign key (PCScore) references cardInfo(cardId)--这种也是给PCScore设置外键约束，对象为cardInfo(cardId)
)

insert into cardInfo values('123456',156,getDate(),default),('123abc',800,default,'东南西北'),('abc123',800,'2001-01-01','山的那一边')
insert into PCInfo values('张三','男',null,2),('李四','男','',3),('王五','男',default,1)

select * from PCInfo
select * from cardInfo

check：条件约束

就是给一列的数据进行了【限制】:alter table 表名 add constraint 随便取约束名 check（限制条件）

语法：
alter table <表名> add constraint <约束名> check( <列名> is [ null | not null ])--限制列可以为空或非空

alter table cardInfo add constraint CK_cardInfo_cardPassword check(len(cardPassword)>=6);--check【审查】（限制长度必须大于等于6个字符）

unique唯一约束：不能有重复值

给某个表某一列添加个【唯一】约束，也就是不能有重复数据

语法:
alter table 表名 add constraint 约束名称 unique（列名）

alter table cardInfo add constraint UQ_pw unique(cardPassword); --这个字段不能有重复值

default：默认值约束

给某个表某一列添加个【默认值】数据

语法:
alter table 表名 add constraint 约束名称 default  默认值 for 列名

alter table cardInfo add constraint DF_stuaddress default '地址不详' for stuaddress; --给表cardInfo，字段stuaddress设置默认值为 '地址不详'。

删除约束统一语法：alter table 表名 drop constraint 约束名

为了更好的查询约束名，提供了一些查询语句

查询唯一约束：unique

SELECT
  tab.name AS [表名],
  idx.name AS [约束名称],
  col.name AS [约束列名]
FROM
  sys.indexes idx
    JOIN sys.index_columns idxCol
      ON (idx.object_id = idxCol.object_id
          AND idx.index_id = idxCol.index_id
          AND idx.is_unique_constraint = 1)
    JOIN sys.tables tab
      ON (idx.object_id = tab.object_id)
    JOIN sys.columns col
      ON (idx.object_id = col.object_id
          AND idxCol.column_id = col.column_id);

查询条件约束：check

SELECT
  tab.name AS [表名],
  chk.name AS [Check约束名],
  col.name AS [列名],
  chk.definition
FROM
  sys.check_constraints chk
    JOIN sys.tables tab
      ON (chk.parent_object_id = tab.object_id)
    JOIN sys.columns col
      ON (chk.parent_object_id = col.object_id
          AND chk.parent_column_id = col.column_id)

查询外键约束：

select
  oSub.name  AS  [子表名称],
  fk.name AS  [外键名称],
  SubCol.name AS [子表列名],
  oMain.name  AS  [主表名称],
  MainCol.name AS [主表列名]
from
  sys.foreign_keys fk 
    JOIN sys.all_objects oSub 
        ON (fk.parent_object_id = oSub.object_id)
    JOIN sys.all_objects oMain
        ON (fk.referenced_object_id = oMain.object_id)
    JOIN sys.foreign_key_columns fkCols
        ON (fk.object_id = fkCols.constraint_object_id)
    JOIN sys.columns SubCol
        ON (oSub.object_id = SubCol.object_id 
            AND fkCols.parent_column_id = SubCol.column_id)
    JOIN sys.columns MainCol
        ON (oMain.object_id = MainCol.object_id 
            AND fkCols.referenced_column_id = MainCol.column_id)