表约束之自定义实现约束规则
来源：程序员人生 发布时间：2015-01-12 08:35:30 阅读次数：3486次
问题1:
/*表名:TAB
ID ParentID Name
1 0 A
2 1 A-B
3 1 A-C
束缚父ID（ParentID）只能为0或TAB.ID记录
*/
方法1:(用函数实现自定义规则)
USE tempdb
go
if OBJECT_ID('TAB','U') is not null
drop table TAB
go
CREATE TABLE TAB(ID int not null unique,ParentID int not null,Name varchar(10) not null)
go
if OBJECT_ID('fn_chk_ParentID','fn') is not null
drop function fn_chk_ParentID
go
create function fn_chk_ParentID(@ParentID int)
returns bit
as
begin
if @ParentID=0 OR exists(select 1 from Tab where ID=@ParentID and ID<>ParentID)
return 1
return 0
end
go
ALTER TABLE TAB ADD CONSTRAINT CHK_TAB_ParentID CHECK(DBO.fn_chk_ParentID(ParentID)=1)
GO
insert into TAB values(1,0,'A'),(2,1,N'A-B'),(3,1,N'A-C'),(4,0,'D')--OK
insert into TAB values(5,5,'D-E')--Error
/*
消息 547，级别 16，状态 0，第 24 行
INSERT 语句与 CHECK 束缚"CHK_TAB_ParentID"冲突。该冲突产生于
数据库"tempdb"，表"dbo.TAB", column 'ParentID'。
语句已终止。
*/
select * from TAB
/*
ID ParentID Name
1 0 A
2 1 A-B
3 1 A-C
4 0 D
*/
drop table Tab
drop function fn_chk_ParentID
方法2：(用触发器实现自定义规则)
USE tempdb
go
if OBJECT_ID('TAB','U') is not null
drop table TAB
go
CREATE TABLE TAB(ID int not null unique,ParentID int not null,Name varchar(10) not null)
go
create trigger tr_cTAB on TAB
instead of insert
as
begin
if exists(select 1 from inserted as i where i.ParentID<>0 AND NOT EXISTS(SELECT 1 FROM TAB WHERE ID=i.ParentID))
BEGIN
RAISERROR (N'ParentID不正确',16,1)
RETURN
END
INSERT INTO TAB(ID,ParentID,Name) SELECT ID,ParentID,Name FROM INSERTED
end
GO
insert into TAB values(1,0,'A')--OK
insert into TAB values(2,1,N'A-B'),(3,1,N'A-C')--OK
insert into TAB values(4,0,'D')--OK
insert into TAB values(5,5,'D-E')--Error
/*
消息 50000，级别 16，状态 1，进程 tr_cTAB，第 31 行
ParentID不正确
*/
select * from TAB
/*
ID ParentID Name
1 0 A
2 1 A-B
3 1 A-C
4 0 D
*/
drop table Tab
问题2：
援用论坛网友提出的问题
http://bbs.csdn.net/topics/390961501
/*表：TAB
billno
BM0001⑵0141211
BM0002⑵0141212
要求substring(billno,3,4)不能重复，即不能增加BM0001⑵0141212
*/
方法1：(不改变表结构的情况下用函数实现自定义规则)
USE tempdb
go
if OBJECT_ID('TAB','U') is not null
drop table TAB
go
CREATE TABLE TAB(billno VARCHAR(50) )
go
if OBJECT_ID('fn_chkBillNo','fn') is not null
drop function fn_chkBillNo
go
create function fn_chkBillNo(@billno varchar(50))
returns bit
as
begin
if exists(select 1 from TAB where SUBSTRING(billno,3,4)=SUBSTRING(@billno,3,4) having count(1)>1)--大于1
return 1
return 0
end
go
ALTER TABLE TAB ADD CONSTRAINT chk_TAB_billno check(dbo.fn_chkBillNo(billno)=0)
go
INSERT INTO TAB(billno) VALUES('BM0001⑵0141211')--OK
INSERT INTO TAB(billno) VALUES('BM0001⑵0141211')--Error
select * from TAB
/*
billno
BM0001⑵0141211
*/
DROP TABLE TAB
drop function fn_chkBillNo
方法2：(不改变表结构的情况下用触发器实现自定义规则)
USE tempdb
go
CREATE TABLE TAB(billno VARCHAR(50) )
go
go
CREATE TRIGGER tr_cTAB ON TAB
INSTEAD OF INSERT
AS
BEGIN
IF EXISTS(SELECT 1 FROM INSERTED AS i WHERE EXISTS(SELECT 1 FROM TAB WHERE SUBSTRING(billno,3,4)=SUBSTRING(i.billno,3,4)))
BEGIN
RAISERROR (N'bill重复',16,1)
RETURN
END
INSERT INTO TAB SELECT billno FROM INSERTED
END
GO
INSERT INTO TAB(billno) VALUES('BM0001⑵0141211')--OK
INSERT INTO TAB(billno) VALUES('BM0001⑵0141211')--Error
select * from TAB
/*
billno
BM0001⑵0141211
*/
DROP TABLE TAB
方法3：(通过改变表结构新增1列计算列实现自定义规则)
USE tempdb
go
CREATE TABLE TAB(billno VARCHAR(50) )
go
ALTER TABLE TAB ADD CHK_billno AS SUBSTRING(billno,3,4) UNIQUE
go
GO
INSERT INTO TAB(billno) VALUES('BM0001⑵0141211')--OK
INSERT INTO TAB(billno) VALUES('BM0001⑵0141211')--Error
select * from TAB
/*
billno
BM0001⑵0141211
*/
DROP TABLE TAB
生活不易，码农辛苦
如果您觉得本网站对您的学习有所帮助,可以手机扫描二维码进行捐赠
