约束 学习
use tempdb
gocreate 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