表约束之自定义实现约束规则

来源：互联网发布：java时间戳转换工具编辑：程序博客网时间：2024/05/01 23:33

问题1:

/*表名:TABIDParentIDName10A21A-B31A-C约束父ID（ParentID）只能为0或TAB.ID记录*/

方法1:(用函数实现自定义规则)

USE tempdbgoif OBJECT_ID('TAB','U') is not nulldrop table TABgoCREATE TABLE TAB(ID int not null unique,ParentID int not null,Name varchar(10) not null)goif OBJECT_ID('fn_chk_ParentID','fn') is not nulldrop function fn_chk_ParentIDgocreate function fn_chk_ParentID(@ParentID int)returns bitasbeginif @ParentID=0 OR exists(select 1 from Tab where ID=@ParentID and ID<>ParentID)return 1return 0endgoALTER TABLE TAB ADD CONSTRAINT CHK_TAB_ParentID CHECK(DBO.fn_chk_ParentID(ParentID)=1)GOinsert into TAB values(1,0,'A'),(2,1,N'A-B'),(3,1,N'A-C'),(4,0,'D')--OKinsert 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/*IDParentIDName10A21A-B31A-C40D*/drop table Tabdrop function fn_chk_ParentID

方法2：(用触发器实现自定义规则)

USE tempdbgoif OBJECT_ID('TAB','U') is not nulldrop table TABgoCREATE TABLE TAB(ID int not null unique,ParentID int not null,Name varchar(10) not null)gocreate trigger tr_cTAB on TABinstead of insertasbeginif 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)        RETURNEND  INSERT INTO TAB(ID,ParentID,Name) SELECT ID,ParentID,Name FROM INSERTEDendGOinsert into TAB values(1,0,'A')--OKinsert into TAB values(2,1,N'A-B'),(3,1,N'A-C')--OKinsert into TAB values(4,0,'D')--OKinsert into TAB values(5,5,'D-E')--Error/*消息 50000，级别 16，状态 1，过程 tr_cTAB，第 31 行ParentID不正确*/select * from TAB/*IDParentIDName10A21A-B31A-C40D*/drop table Tab

问题2：

引用论坛网友提出的问题

http://bbs.csdn.net/topics/390961501

/*表：TABbillnoBM0001-20141211BM0002-20141212要求substring(billno,3,4)不能重复，即不能增加BM0001-20141212*/

方法1：(不改变表结构的情况下用函数实现自定义规则)

USE tempdbgoif OBJECT_ID('TAB','U') is not nulldrop table TABgoCREATE TABLE TAB(billno VARCHAR(50) )goif OBJECT_ID('fn_chkBillNo','fn') is not nulldrop function fn_chkBillNogocreate function fn_chkBillNo(@billno varchar(50))returns bitasbeginif exists(select 1 from TAB where SUBSTRING(billno,3,4)=SUBSTRING(@billno,3,4) having count(1)>1)--大于1return 1return 0endgoALTER TABLE TAB ADD CONSTRAINT chk_TAB_billno check(dbo.fn_chkBillNo(billno)=0)goINSERT INTO TAB(billno) VALUES('BM0001-20141211')--OKINSERT INTO TAB(billno) VALUES('BM0001-20141211')--Error select * from TAB /*billnoBM0001-20141211*/DROP TABLE TABdrop function fn_chkBillNo

方法2：(不改变表结构的情况下用触发器实现自定义规则)

USE tempdbgo CREATE TABLE TAB(billno VARCHAR(50) )gogoCREATE TRIGGER tr_cTAB ON TABINSTEAD OF INSERTAS 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 INSERTEDEND GOINSERT INTO TAB(billno) VALUES('BM0001-20141211')--OKINSERT INTO TAB(billno) VALUES('BM0001-20141211')--Errorselect * from TAB /*billnoBM0001-20141211*/DROP TABLE TAB

方法3：(通过改变表结构新增一列计算列实现自定义规则)

USE tempdbgo CREATE TABLE TAB(billno VARCHAR(50) )goALTER TABLE TAB ADD CHK_billno AS SUBSTRING(billno,3,4) UNIQUEgoGOINSERT INTO TAB(billno) VALUES('BM0001-20141211')--OKINSERT INTO TAB(billno) VALUES('BM0001-20141211')--Errorselect * from TAB /*billnoBM0001-20141211*/ DROP TABLE TAB

1 0