A common scenario we come across is the requirement to implement a constraint on a table to enforce a business rule based on another table. Though this can be implemented by means of trigger its not so efficient at times especially when table is target of batch DML operations. In such cases, one way of implementing this is by defining a CHECK constraint based on a UDF. This post deals with illustration of such a scenario.
A typical scenario that we come across in retail domain is order allocation process. One prerequisite check we need to do before allocation is whether we’ve enough quantity of item in stock if not order cannot be fulfilled. This can be implemented by mean of similar CHECK constraint
See illustration below
CREATE TABLE Product
(
ProductID int IDENTITY(1,1) PRIMARY KEY,
ProductName varchar(100),
QtyInStock int
)
CREATE TABLE Orders
(
OrderID int IDENTITY(1,1) PRIMARY KEY,
CustName varchar(100),
OrderDate date
)
CREATE TABLE OrderItem
(
OrderItemID int IDENTITY(1,1),
OrderID int REFERENCES Orders(OrderID),
ProductID int REFERENCES Product(ProductID),
Qty int
)
So we consider three tables Product,Order and OrderItems for this example.
Order will have order details with individual items stored in OrderItems table. OrderItems is linked with Order and Product tables by means of foreign keys. Now we need to ensure that Order can be allocated only if Qty in order is within Order in stock. We can implement this business rule by means of CHECK constraint based on UDF. The UDF will be as follows
CREATE FUNCTION dbo.CheckQtyAlloc
(
@ProductID int,
@OrderQty int
)
RETURNS bit
AS
BEGIN
DECLARE @Alloc bit
SELECT @Alloc = CASE WHEN QtyInStock >= @OrderQty
THEN 1 ELSE 0 END
FROM product
WHERE ProductID=@ProductID
RETURN (@Alloc)
END
As you see from above, the UDF takes ProductID and OrderQty as parameters and check if ordered qty is within qunatity in stock. Based on result it returns a boolean value to indicate if allocation is possible. This UDF can be linked to CHECK constraint as follows
ALTER TABLE OrderItem ADD CONSTRAINT Chk_Order_Alloc CHECK (dbo.CheckQtyAlloc(ProductID,Qty)=1)
Now lets try inserting values into tables
INSERT product (ProductName,QtyInStock)
VALUES (‘Product A’,100),
(‘Product B’,120),
(‘Product C’,150),
(‘Product D’,25)
DECLARE @OrderID int
INSERT Orders(CustName,OrderDate)
VALUES (‘ABC Corp’,GETDATE())
SELECT @OrderID = SCOPE_IDENTITY()
INSERT OrderItem (OrderID,ProductID,Qty)
VALUES (@OrderID,1,100)
INSERT OrderItem (OrderID,ProductID,Qty)
VALUES (@OrderID,2,150)
INSERT OrderItem (OrderID,ProductID,Qty)
VALUES (@OrderID,3,100)
The result will be as follows
Msg 547, Level 16, State 0, Line 8
The INSERT statement conflicted with the CHECK constraint “Chk_Order_Alloc”. The conflict occurred in database “VisRSSvr”, table “dbo.OrderItem”.
The statement has been terminated.
Now if you see the inserted data it will be as follows
As you see from the above the product with ID 2 was not inserted as its Qty ordered was above that in stock ( 150 as against 120 in stock) and was violating our created check constraint.
This illustration shows a method of enforcing business rule involving multiple tables by means of CHECK constraint based on a UDF.
It has come handy for me in lots of occasions and I feel it will certainly benefits others too in similar scenarios.
