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.
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!