Table constraint-based custom implementation constraint rules and constraint rules

Table constraint-based custom implementation constraint rules and constraint rules

Question 1:

/* Table Name: The TABIDParentIDName10A21A-B31A-C constraint parent ID (ParentID) can only be 0 or TAB. ID record */

Method1: (use functions to implement Custom Rules)

USE tempdbgoif OBJECT_ID ('tab', 'U') is not nullddrop 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 1 return 0 endgoALTER Table tab add constraint CHK_TAB_ParentID CHECK (DBO. fn_chk_ParentID (ParentID) = 1) GOinsert into TAB values (, 'A'), (, N 'a-B, N 'a-C'), (547, 'D') -- OKinsert into TAB values (, 'd-E ') -- Error/* message, level 16, the INSERT statement in the status 0 and 24th conflict with the CHECK constraint "CHK_TAB_ParentID. This conflict occurs in the Database "tempdb", table "dbo. TAB", column 'parentid '. The statement has been terminated. */Select * from TAB/* IDParentIDName10A21A-B31A-C40D */drop table Tabdrop function fn_chk_ParentID

Method 2: (Use triggers to implement Custom Rules)

USE tempdbgoif OBJECT_ID ('tab', 'U') is not nullddrop table TABgoCREATE table tab (ID int not null unique, ParentID int not null, Name varchar (10) not null) gocreate trigger tr_alb 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 incorrect ',) returnend insert into tab (ID, ParentID, Name) select id, ParentID, Name FROM INSERTEDendGOinsert into TAB values, 'A') -- OKinsert into TAB values (, N 'a-B '), (, N 'a-C') -- OKinsert into TAB values, 'D') -- OKinsert into TAB values (50000, 'd-E ') -- Error/* message, level 16, status 1, process tr_ctl, row 31st ParentID incorrect */select * from TAB/* IDParentIDName10A21A-B31A-C40D */drop table Tab

Question 2:

Reference questions raised by Forum users

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

/* Table: TABbillnoBM0001-20141211BM0002-20141212 requirements substring (billno, 3, 4) cannot be repeated, that is, cannot add BM0001-20141212 */

Method 1: (use functions to implement custom rules without changing the table structure)

USE tempdbgoif OBJECT_ID ('tab', 'U') is not nullddrop 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) -- greater than 1 return 1 return 0 endgoALTER 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

Method 2: Use triggers to implement custom rules without changing the table structure)

USE tempdbgo create table tab (billno VARCHAR (50 )) gogoCREATE TRIGGER tr_alb 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 repeated ', 20141211) return end insert into tab select billno from insertedend goinsert into tab (billno) VALUES ('bm0001-100 ') -- okinsert into tab (billno) VALUES ('bm0001-000000') -- Errorselect * from TAB/* billnoBM0001-20141211 */DROP TABLE TAB

Method 3: (add a column of calculated columns to change the table structure to implement Custom Rules)

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