Skip to content

shannonlowder.com

Menu
  • About
  • Biml Interrogator Demo
  • Latest Posts
Menu

70-432: Know Your Constraints

Posted on April 25, 2011September 27, 2011 by slowder

When you’re getting ready for the 70-433, you’re going to want to be familiar with constraints. They’re useful tools for setting up data integrity checks inside the database itself.  Yeah, I’m going to reference The Matrix here, but learning to manipulate Constraints is a little like learning to bend spoons.

There Is No Spoon
The Matrix at MOVIECLIPS.com

I know some of you may argue that business logic checks should be outside the database, in a middle-tier layer.  But this example ignores that discussion, and provides you some practice with constraints.

You work at AdventureWorks, and when first thing in the morning you’re called into a meeting.  Management has decided they’re no longer going to charge shipping fees for the holiday shopping season.  No fees will be charged for shipments leaving during November or December each year.

The problem is when a sales person tried to run the following insert:
INSERT INTO Sales.SalesOrderHeader
(
RevisionNumber, OrderDate, DueDate, ShipDate, [Status], OnlineOrderFlag
, CustomerID, ContactID, BillToAddressID, ShipToAddressID, ShipMethodID
, SubTotal, TaxAmt, Freight, rowguid,ModifiedDate
)
select
RevisionNumber = 1, OrderDate = GETDATE(), DueDate = ’11/15/2011′
, ShipDate = ’11/1/2011′, [Status] = 1, OnlineOrderFlag = 1
, CustomerID = 676, ContactID = 378, BillToAddressID = 985
, ShipToAddressID = 985, ShipMethodID = 5, SubTotal = 24643.93
, TaxAmt = 1971.51, Freight = 0, rowguid = NEWID()
, ModifiedDate = GETDATE()

They received the error message:

Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint “CK_SalesOrderHeader_Freight”. The conflict occurred in database “AdventureWorks”, table “Sales.SalesOrderHeader”, column ‘Freight’.
The statement has been terminated.

So in order to change make the changes that will allow them to offer free shipping, you have to know a few things about constraints.  First, you have to know that you can’t ALTER a constraint.  You must DROP the CONSTRAINT, then CREATE the CONSTRAINT.

Let’s start with that.

IF  EXISTS (
SELECT *
FROM sys.check_constraints
WHERE
object_id = OBJECT_ID(N'[Sales].[CK_SalesOrderHeader_Freight]’)
AND parent_object_id = OBJECT_ID(N'[Sales].[SalesOrderHeader]’))

ALTER TABLE [Sales].[SalesOrderHeader] DROP CONSTRAINT [CK_SalesOrderHeader_Freight]
GO

Of course, you could simply run the ALTER TABLE…DROP CONSTRAINT statement.  But wrapping the whole thing in an IF EXISTS check will make sure it’s there first.  SSMS will write this script for you if you open up navigate to the table, then open constraints, then right click on the constraint and choose “Script Constraint as” -> DROP To.

Now that the Constraint is removed, our insert would work.  But they could just easily entered a ship date of July 1st, and entered no shipping.  We have to CHECK for that condition and prevent it.

Now you’ll need to know two things.
How SQL evaluates logical AND and OR operations together.
How to CREATE a CONSTRAINT.

First, Let’s talk about how AND and OR work together.  We’re checking the ShipDate and the Freight.  In one case if they are getting a shipment in November or December, there is no shipping.  BUT if they are shipping any other time of the year, there must be a shipping fee.

So you have the conditions
MONTH(shipDate) NOT IN (11,12) AND  Freight>=(0.00)
MONTH(shipDate) IN (11,12) AND Freight = 0

If you simply slapped an OR between these two it would actually select true for any case where freight is not free or the month is outside November or December.  The problem is that would allow a freight charge during November or December.

To make sure you get exactly the logic you want.  You should use Parenthesis.  That way you can set up boundaries for those evaluations that have to both be true.

So I’m going to check the following logic:

ALTER TABLE Sales.SalesOrderHeader  WITH CHECK ADD
CONSTRAINT CK_SalesOrderHeader_Freight
CHECK (
( MONTH(shipDate) NOT IN (11,12) AND  Freight>=(0.00) )
OR
( MONTH(shipDate) IN (11,12) AND Freight = 0 )
)
GO

Once we run that script, we get another error:

Msg 547, Level 16, State 0, Line 1
The ALTER TABLE statement conflicted with the CHECK constraint “CK_SalesOrderHeader_Freight”. The conflict occurred in database “AdventureWorks”, table “Sales.SalesOrderHeader”.

The reason is, the CHECK constraint will now fail for some rows.  To see how many, do a quick count.

SELECT COUNT(*)
FROM Sales.SalesOrderHeader
WHERE (
( MONTH(shipDate) NOT IN (11,12) AND  Freight>=(0.00) )
OR
( MONTH(shipDate) IN (11,12) AND Freight = 0 )
)
–26113 in my instance of SQL 2008R2

Since This new rule should only apply to sales going forward, we should add one more bit of logic to our check, then we’ll be finished.

ALTER TABLE Sales.SalesOrderHeader  WITH CHECK ADD
CONSTRAINT CK_SalesOrderHeader_Freight
CHECK (
orderDate < ‘4/25/2011’
OR
( MONTH(shipDate) NOT IN (11,12) AND  Freight >= (0.00) )
OR
( orderDate >= ‘4/25/2011’ AND MONTH(shipDate) IN (11,12) AND Freight = 0 )
)

By adding the initial orderdate < ‘4/25/2011′  We’ve basically said, this rule will only apply to new orders coming in. Now when we run the following query:

INSERT INTO Sales.SalesOrderHeader
(
RevisionNumber, OrderDate, DueDate, ShipDate, [Status], OnlineOrderFlag
, CustomerID, ContactID, BillToAddressID, ShipToAddressID, ShipMethodID
, SubTotal, TaxAmt, Freight, rowguid,ModifiedDate
)
select
RevisionNumber = 1, OrderDate = GETDATE(), DueDate = ’11/15/2011′
, ShipDate = ’11/1/2011’, [Status] = 1, OnlineOrderFlag = 1
, CustomerID = 676, ContactID = 378, BillToAddressID = 985
, ShipToAddressID = 985, ShipMethodID = 5, SubTotal = 24643.93
, TaxAmt = 1971.51, Freight = 0, rowguid = NEWID()
, ModifiedDate = GETDATE()

We’re greeted by the lovely:

(1 row(s) affected)

And with that, we’ve made the change management requested.  If you’re preparing for the 70-433, you’ll need to be comfortable making this kind of change on your own servers.  If you have any questions, please send them in.  I’m here to help!

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Recent Posts

  • A New File Interrogator
  • Using Generative AI in Data Engineering
  • Getting started with Microsoft Fabric
  • Docker-based Spark
  • Network Infrastructure Updates

Recent Comments

  1. slowder on Data Engineering for Databricks
  2. Alex Ott on Data Engineering for Databricks

Archives

  • July 2023
  • June 2023
  • March 2023
  • February 2023
  • January 2023
  • December 2022
  • November 2022
  • October 2022
  • October 2018
  • August 2018
  • May 2018
  • February 2018
  • January 2018
  • November 2017
  • October 2017
  • September 2017
  • August 2017
  • June 2017
  • March 2017
  • February 2014
  • January 2014
  • December 2013
  • November 2013
  • October 2013
  • August 2013
  • July 2013
  • June 2013
  • February 2013
  • January 2013
  • August 2012
  • June 2012
  • May 2012
  • April 2012
  • March 2012
  • February 2012
  • January 2012
  • December 2011
  • November 2011
  • October 2011
  • September 2011
  • August 2011
  • July 2011
  • June 2011
  • May 2011
  • April 2011
  • March 2011
  • February 2011
  • January 2011
  • December 2010
  • November 2010
  • October 2010
  • September 2010
  • August 2010
  • July 2010
  • June 2010
  • May 2010
  • April 2010
  • March 2010
  • January 2010
  • December 2009
  • November 2009
  • October 2009
  • September 2009
  • August 2009
  • July 2009
  • June 2009
  • May 2009
  • April 2009
  • March 2009
  • February 2009
  • January 2009
  • December 2008
  • November 2008
  • October 2008
  • September 2008
  • August 2008
  • July 2008
  • June 2008
  • May 2008
  • April 2008
  • March 2008
  • February 2008
  • January 2008
  • November 2007
  • October 2007
  • September 2007
  • August 2007
  • July 2007
  • June 2007
  • May 2007
  • April 2007
  • March 2007
  • February 2007
  • January 2007
  • December 2006
  • November 2006
  • October 2006
  • September 2006
  • August 2006
  • July 2006
  • June 2006
  • May 2006
  • April 2006
  • March 2006
  • February 2006
  • January 2006
  • December 2005
  • November 2005
  • October 2005
  • September 2005
  • August 2005
  • July 2005
  • June 2005
  • May 2005
  • April 2005
  • March 2005
  • February 2005
  • January 2005
  • November 2004
  • September 2004
  • August 2004
  • July 2004
  • April 2004
  • March 2004
  • June 2002

Categories

  • Career Development
  • Data Engineering
  • Data Science
  • Infrastructure
  • Microsoft SQL
  • Modern Data Estate
  • Personal
  • Random Technology
  • uncategorized
© 2025 shannonlowder.com | Powered by Minimalist Blog WordPress Theme