資料表條件約束之自訂實現約束規則，約束規則

資料表條件約束之自訂實現約束規則，約束規則

問題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