Enforce Business Rules in a SQL Server Database

By: Christopher Mohr   |   Updated: 2024-10-31   |   Comments   |   Related: > Error Handling

Problem

Triggers and constraints are valuable features of database management systems (DBMS) for enforcing data integrity and business rules. They can reduce the cost and time it would take to duplicate this functionality by writing source code to handle it, which also helps data consistency. But, in some situations, triggers and constraints may not be the best methods, and some environments may not allow developers to use them at all. What can I use instead?

Solution

If you do not have the option of using triggers or constraints to enforce business rules or data integrity in your work but still need data to comply with such requirements, a combination of left joins with exclusions, error handling, and some intermediate tables may help solve the problem.

Why You Might Not Be Able to Use Triggers or Constraints

Developers may not be able to rely on triggers or constraints for several reasons:

1. Developers are not allowed to add them to tables because of policies or permissions.
2. The development team leadership may have decided not to use triggers because of their disadvantages, as Daniel Farina points out.
3. Cultural or philosophical preference that it's better to 'let the data in' and add another layer of functionality to fix it after the fact than to be overly restrictive and not let any problem data in at all.
4. Data is being imported from a source that does not enforce referential integrity, so cleanup layers (as mentioned in the previous reason) become a requirement.

Anything You Want to Be an Error Can Be

When I started as a developer, I used to think error handling was a safety net to protect everyone from 'bad things happening' when using an application. But this is a very limiting view that does not consider how it can be used strategically.

Some errors are inherently bad, like division by zero or attempting to put data into a column with an incompatible data type (e.g., character data into an integer column). SQL Server is quick to catch these errors, which I will refer to as DB engine errors.

Other errors are not inherently bad as far as the DBMS is concerned but may represent conditions developers need to prevent. For example, a DBMS for an online gambling site may be perfectly OK adding a customer if there were no problems with the underlying data that would cause DB engine errors.

However, if the customer resided in a state that outlawed online gambling, the site's management could be in a lot of legal hot water if they do not block that customer from setting up an account. This would require some kind of notification that there was a problem and prevent the account from being set up.

If you can make residing in a certain state an error condition, then you can make anything you want an error condition. I will refer to these as custom errors.

DB engine errors will occur automatically while custom errors must be tested for, and when found, an error needs to be raised or thrown.

Example: Widgets Unlimited and State Laws

In this example, assume you are writing SQL for a widget company, Widgets Unlimited (WU), that makes several models of widgets. The company has been successful with these products to the point that they can now advertise on a well-known video hosting site.

An issue has come up that WU must address ASAP. Out of safety concerns, the State of Illinois has outlawed the sale of the Super Deluxe widget to its residents. The law goes into effect immediately, instead of the usual time lag where most new laws become effective the following January 1 or July 1.

Immediate compliance is critical because regulators can assess stiff fines for non-compliance. WU does not want to lose business in a highly populated state like Illinois from a regulatory ban.

We will make the following assumptions (mostly for the sake of simplicity) in solving this problem:

1. Only one item per order is allowed.
2. Every customer pays the same prices; no custom pricing is allowed.
3. Orders are only taken within the U.S.
4. It might be preferable to block Illinois residents from ordering the outlawed widget at the UI level. Since the immediacy of the law going into effect (and this is an SQL site, not a web app site), the expedient approach is to allow the order to go through, examine it with SQL code, and reject it if it does not comply with the hypothetical Illinois law.
5. While error handling will be used in this example, it will not be discussed extensively. Joydip Kanjilal has a great article on error handling.
6. You are not allowed to alter the production tables. They are created by another vendor, and altering them undermines the vendor's upgrade process.
7. You can create your own tables but cannot use constraints or triggers with them.
8. In this example, we are not concerned with processing the valid orders. Assume that if a row of data makes it successfully to Orders, the order will be processed, i.e., the customer will be billed, and the item will be packaged and shipped with little or no delay.
9. Other limitations are imposed for simplicity's sake, such as only first and last names and simple street addresses without secondary addresses, like suites, apartments, or unit numbers.

Creating and Populating Example Data

The following script allows you to create the database and tables and populate the table with sample data. There is some data that would potentially violate referential integrity and Illinois state law:

-- www.mssqltips.com
CREATE DATABASE dbWidgetsUnlimited
GO
 
USE dbWidgetsUnlimited
GO
 
-- create the production tables with constraints
 
CREATE TABLE Items
(
   ItemNo int NOT NULL PRIMARY KEY,
   ItemDesc varchar(50) NOT NULL,
   ItemPrice money NOT NULL
)
 
CREATE TABLE Customers
(
   CustNo int NOT NULL PRIMARY KEY,
   CustFName varchar(50) NOT NULL,
   CustLName varchar(50) NOT NULL,
   CustStreetAddr varchar(50) NOT NULL,
   CustCity varchar(50) NOT NULL,
   CustState varchar(2) NOT NULL,
   CustZip varchar(15) NOT NULL
)
 
CREATE TABLE Orders
(
   OrderNo int NOT NULL PRIMARY KEY,
   CustNo int NOT NULL,
   ItemNo int NOT NULL,
   OrderDateTime datetime
)
 
ALTER TABLE Orders
ADD CONSTRAINT FK_Orders_Customers FOREIGN KEY (CustNo)
REFERENCES Customers (CustNo)
 
ALTER TABLE Orders
ADD CONSTRAINT FK_Orders_Items FOREIGN KEY (ItemNo)
REFERENCES Items (ItemNo)
 
 
-- these tables are not production; they are created by the developer
 
CREATE TABLE OrdersStaged
(
   OrderNo int NOT NULL,
   CustNo int NOT NULL,
   ItemNo int NOT NULL,
   OrderDateTime datetime
)
 
CREATE TABLE OrderErrors
(
   OrderNo int NOT NULL,
   CustNo int NOT NULL,
   ItemNo int NOT NULL,
   ErrorDesc varchar(255) NOT NULL
)
 
-- add some data to these tables to illustrate the article's concepts
 
INSERT INTO Items
   (ItemNo, ItemDesc, ItemPrice)
VALUES
   (10001, 'Standard Widget', $59.99),
   (10002, 'Deluxe Widget', $79.99),
   (10003, 'Super Deluxe Widget', $99.99)
   
INSERT INTO Customers
   (CustNo, CustFName, CustLName, CustStreetAddr, CustCity, CustState, CustZip)
VALUES
   (20001, 'Fred', 'Richards', '30000 W. Main St', 'Greenwood', 'IN', '46142'),
   (20002, 'Elmer', 'Zachary', '98765 W. Elm', 'Effingham', 'IL', '62401'),
   (20003, 'John', 'Snyder', '12345 E. Douglas', 'Anaheim', 'CA', '92807'),
   (20004, 'Thomas', 'Fricke', '24999 S. Broadway', 'Wichita', 'KS', '67219')
 
INSERT INTO OrdersStaged
   (OrderNo, CustNo, ItemNo, OrderDateTime)
VALUES
   -- legitimate order, Indiana has no widget restrictions
   (240001, 20001, 10001, '4/15/2024 11:45 AM'),   
 
   -- ILLEGAL: super deluxe widgets are illegal in Illinois
   (240002, 20002, 10003, '5/1/2024 1:37 PM'),      
 
   -- legit: Fred from Indiana liked his standard widget so much he upgraded
   (240003, 20001, 10003, '5/15/2024 12:07 PM'),   
 
   -- legit: California has no widget restrictions
   (240004, 20003, 10002, '5/16/2024 3:12 PM'),   
 
   -- legit: money's tight so Tom in Wichita gets a standard widget
   (240005, 20004, 10001, '6/23/2024 9:36 AM'),   
 
   -- RI error: customer number invalid
   (240006, 20099, 10001, '7/2/2024 12:00 AM'),
   
   -- RI error: item number invalid
   (240007, 20004, 10009, '7/7/2024 10:47 AM')
         

The Items and Customers tables store production data. In and of themselves, there is nothing wrong with the data they contain. We start with empty Orders and OrderErrors tables.

OrdersStaged is a clone of the production Orders table but without constraints. It is in this table where the potential problems lie. There is data that could lead to compliance problems and data that could cause foreign key violations if it makes it to the Orders table to be processed.

A Possible Solution

Although mentioned earlier, the OrdersStaged table is part of the solution (in a way). It allows us to create a table structurally the same as the Orders table but without fear of many common errors occurring.

We can use OrdersStaged along with error handling and left joins with exclusion (LJWE) to provide a mechanism for addressing data problems and comply with the limitations we are subject to.

If LJWE is something that you struggle to understand, the following Venn diagram may help:

In a regular LEFT JOIN between the table OrdersStaged and OrdersErrors, sections A and B (above) are in the results. The query would appear as follows:

-- www.mssqltips.com
SELECT
   S.OrderNo
FROM
   OrdersStaged S
LEFT JOIN
   OrderErrors E
ON
   S.OrderNo = E.OrderNo 

Given the data added earlier, this would return all order numbers in OrdersStaged, OrderNo values 240001 through 240007, whether they exist in OrderErrors or not.

The following code would give us only the OrdersStaged OrderNo values that represent errors:

-- www.mssqltips.com
SELECT
   S.OrderNo
FROM 
   OrdersStaged S
INNER JOIN
   OrderErrors E
ON
   S.OrderNo = E.OrderNo 

This would give us 240002, 240006, and 240007 and would be represented by region B in the above Venn diagram.

We would change the JOIN to a RIGHT JOIN if we wanted the results represented by C, OrderNo values in the OrderErrors table that do not appear in OrdersStaged. However, this is not helpful to us and would likely be the result of error data that was obsolete.

What we really want is the data represented by region A only, which consists of OrderNo values in OrdersStaged that are not erroneous. These are the only values that we can safely add to the Orders table. The code for selecting this data is as follows:

-- www.mssqltips.com
SELECT
   S.OrderNo,
   S.CustNo,
   S.ItemNo,
   S.OrderDateTime
FROM
   OrdersStaged S
LEFT JOIN
   OrderErrors E
ON
   S.OrderNo = E.OrderNo
WHERE
   E.OrderNo IS NULL

It may seem a little counterintuitive at first, but the WHERE clause is the key here. When E.OrderNo IS NOT NULL, it has a matching row in OrdersStaged, representing an error condition. So, we want the ones where E.OrderNo IS NULL for the good data to go through.

As a result, one possible solution is:

-- www.mssqltips.com
TRUNCATE TABLE OrderErrors 
 
DECLARE
   @ErrorRowsCount int
 
 
BEGIN TRY
 
   -- look for illegal widget orders from Illinois
   INSERT INTO OrderErrors 
      (OrderNo, CustNo, ItemNo, ErrorDesc)
   SELECT 
      S.OrderNo,
      C.CustNo,
      I.ItemNo,
      'Super Deluxe Widget is illegal in Illinois'
   FROM
      OrdersStaged S
   INNER JOIN
      Customers C
   ON
      S.CustNo = C.CustNo
   INNER JOIN
      Items I
   ON
      S.ItemNo = I.ItemNo
   WHERE
      S.ItemNo = 10003 AND -- super deluxe widget
      C.CustState = 'IL'
 
   -- look for invalid Item numbers that would cause RI errors 
   -- if added to production tables
   INSERT INTO OrderErrors 
      (OrderNo, CustNo, ItemNo, ErrorDesc)
   SELECT 
      S.OrderNo,
      S.CustNo,
      S.ItemNo,
      'Item ' + CONVERT(varchar(25), S.ItemNo) + ' does not exist in the Items table' AS ErrorDesc
   FROM
      OrdersStaged S
 
   -- Left join with exceptions to get any staged orders whose item numbers
   -- are not found in the Items table
   LEFT JOIN
      Items I
   ON
      S.ItemNo = I.ItemNo
   WHERE
      I.ItemNo IS NULL
 
 
   -- look for invalid Customer numbers that would cause 
   -- RI errors if added to production tables
   INSERT INTO OrderErrors 
      (OrderNo, CustNo, ItemNo, ErrorDesc)
   SELECT 
      S.OrderNo,
      S.CustNo,
      S.ItemNo,
      'Customer ' + CONVERT(varchar(25), S.CustNo) + ' does not exist in the Customers table' AS ErrorDesc
   FROM
      OrdersStaged S
 
   -- another LJWE
   LEFT JOIN
      Customers C
   ON
      S.CustNo = C.CustNo
   WHERE
      C.CustNo IS NULL
 
   SELECT * FROM OrderErrors
 
 
   -- LJWE: Add to the production Orders table only those staged order 
   -- rows that do not have order numbers in common with OrderErrors. 
   -- Once these rows are in the Orders table, they can be processed later
 
   INSERT INTO Orders
   (
      OrderNo,
      CustNo,
      ItemNo,
      OrderDateTime
   )
   SELECT
      S.OrderNo,
      S.CustNo,
      S.ItemNo,
      S.OrderDateTime
   FROM
      OrdersStaged S
   LEFT JOIN
      OrderErrors E
   ON
      S.OrderNo = E.OrderNo
   WHERE
      E.OrderNo IS NULL
 
   -- see if we have errors in the error table, if any exist then throw an error
   -- this way the user is actively notified that there was an error when one
   -- or more occur
   SELECT @ErrorRowsCount = COUNT(*) FROM OrderErrors
 
   IF @ErrorRowsCount = 0 
      PRINT 'Process completed, no errors occurred'
   ELSE
      THROW 60000, 'One or more errors occurred, check the OrderErrors table for details', 1
 
END TRY
BEGIN CATCH
   -- Beyond the scope of this article, but here you could put 
   -- additional code for dealing with error conditions, maybe 
   -- send an email to a manager or admin
 
   THROW    -- Re-throw the error so the error message appears 
END CATCH 

Most of the code is in a TRY-CATCH block for error handling. At the end of the main section of code, we check for rows in OrderErrors and throw an error if any are found. It is important to note that the way this code is structured, any non-erroneous rows from OrderStaged will be INSERted into Orders without regard to any erroneous rows.

Given the data added earlier, orders 240001, 240003, 240004, and 240005 from OrdersStaged will be added to the Orders table.

This approach allows us to prevent potential Referential Integrity violations and data from being processed that violates legal issues, be notified when there are erroneous rows of data, and identify the offending rows, all without disrupting the execution of our script or violating the hypothetical vendor's restrictions.

Next Steps

• Check out these related tips:
  • SQL Server Triggers
  • SQL Server Constraints
  • SQL Server Table Design Guidelines - Data Types, Designing for Deletes and Primary Keys
  • SQL Server Triggers Tutorial

About the author

Christopher Mohr is an SQL and C# developer in the Indianapolis area. He likes the challenge of solving real-world problems with both languages and is learning more about cybersecurity.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips