表約束之自定義實現約束規則
來源：程序員人生 發布時間：2015-01-12 08:35:30 閱讀次數：3728次
問題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
生活不易，碼農辛苦
如果您覺得本網站對您的學習有所幫助,可以手機掃描二維碼進行捐贈
