约束 学习

约束 学习

use tempdb

go
create table testtable
(
iid int check(iid>50 and iid<100)
)
go
 ----------------------check约束---------------------
 --开启约束检查--------------------------------------
 alter table TableName check constraint all
 --关闭约束检查--------------------------------------
 alter table TableName nocheck constraint all
 --是否检查原来的表中的数据  在  studio 约束管理窗口选择 
 --在【创建或从新启动时检查现有数据】-------对现有的数据检查
 --添加约束
 alter table testtable add constraint chk_1 check(iid>1 and iid<1000)
 --删除约束
 alter table testtable drop constraint chk_1 
 --检查数据库中的约束
 select * from sys.check_constraints 
 go
 ----------------------primarykey约束---------------------
 alter table testtable alter column iid int not null
 alter table testtable add  constraint Pk_1 primary key(iid)
 select * from sys.key_constraints
 ----------------------primarykey约束---------------------
 ----------------------默认约束---------------------------
 alter table testtable add constraint df_1 default(1) for iid
 select * from sys.default_constraints
 ----------------------默认约束---------------------------
 -------------------外键约束------------------------------
 alter table 总概算条目 add constraint FY_1 foreign key (条目序号) references 章节表(条目序号)
 create table MainType
(
typeid int primary key,
[name] nvarchar(20)
)


create table subMainType
(
SubTypeId int primary key,
typeid int ,
[Subname] nvarchar(20)
)
go
alter table subMainType add constraint Fk_1 foreign key(typeid) references MainType(Typeid)
go
insert into MainType(typeid,name) values(1,'钢铁')
go
insert into subMainType(SubTypeId,typeid,Subname) values(1,1,'钢筋')
insert into subMainType(SubTypeId,typeid,Subname) values(2,2,'钢构件')
go
-- 删除 从新添加
alter table subMainType nocheck constraint Fk_1
alter table subMainType check constraint Fk_1
alter table subMainType drop constraint FK_1
update MainType set typeid=2
go
alter table subMainType with check add  constraint  FK_1 foreign key(typeid) references Maintype(typeid) on update cascade
on delete set null
go
select * from subMainType
go
delete from  MainType
-------------------外键约束------------------------------
go 
 select * from sys.objects  where name='MainType'
go
alter table MainType add abc int default 1 with values
go
select * from MainType
insert into MainType(typeid,name) values(1,'11')
go
truncate table MainType
delete from MainType where typeid=1
go
select * from MainType
go
select * from subMainType
go
alter table maintype  drop constraint FK_1
alter table maintype  drop constraint fk_1
go