T-SQL数据库约束学习笔记

--创建数据库
create database test
go
create table table1
(
   book_ID int primary key not null,
   book_name varchar(50) not null,
   book_from varchar(100) not null
)
go
use test
select * from table1
insert into table1 values ('01','数据结构','图书馆一楼')
go
alter table table1 add book_infomation varchar(200)
insert into table1 (book_ID,book_name,book_from) values('02','网络技术','图书馆二楼')
go
alter table table1 add constraint DF_table1 default '没有信息' for book_infomation
insert into table1 (book_ID,book_name,book_from) values('03','西方经济原理','图书馆六楼')
go
select * from table1
------------------分割线 :D------------------

--check约束
alter table table1 add book_sort varchar(20)
go
alter table table1 add constraint DF_check check (book_sort in('学习类','娱乐类','休闲类'))
alter table table1 add book_price int
go
alter table table1 add constraint DF_price check (book_price>'0' and book_price<'100')

--创建多个约束
create table desk
(
   desk_ID int  not null,
   desk_name varchar(50) not null,
   desk_from varchar(100) not null,
  primary key (desk_ID,desk_name)
)
GO
select * from desk

--外键约束
alter table table1 add constraint PK_table1_desk foreign key (book_ID) references dbo.desk(desk_ID) on cascade
--on cascade这句话有点强,可以让约束的两个列一直保持数据一致.