THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Alexander Kuznetsov

Two suggestions to enhance constraints in SQL Server

We can tuck any logical expressions in CHECK constraints, but all foreign keys are currently capable of right now is to verify that one or more column values are equal. In many cases it would be very useful to have foreign keys use more complex logical expressions. Also in some cases the ability to create constraints on indexed views would be very handy too. I will provide examples and hopefully we will come up with Connect items to vote for.

 

When more complex logical expressions in foreign keys are useful

 

Consider the following simple common sense rule: the maximum current of your device cannot exceed the maximum current of the circuit you plug it into. Suppose that the following tables store data about circuits and devices:

 

CREATE TABLE Data.Curcuits(CurcuitID INT NOT NULL

  CONSTRAINT PK_Curcuits PRIMARY KEY,

  MaximumCurrent INT NOT NULL,

  Description VARCHAR(100) NOT NULL);

GO

INSERT INTO Data.Curcuits(CurcuitID,

  MaximumCurrent,

  Description)

SELECT 1, 25, 'Deck and Garage';

GO

CREATE TABLE Data.Devices(DeviceID INT NOT NULL

  CONSTRAINT PK_Devices PRIMARY KEY,

  CurcuitID INT NULL,

  MaximumCurrent INT NOT NULL,

  Description VARCHAR(100) NOT NULL,

  CONSTRAINT FK_Devices_Curcuits FOREIGN KEY(CurcuitID)

    REFERENCES Data.Curcuits(CurcuitID)

  );

GO

 

It would be very convenient to issue a simple command and implement this business rule:

 

ALTER TABLE Data.Devices ADD CONSTRAINT FK_Devices_Curcuits

  FOREIGN KEY(CurcuitID, MaximumCurrent)

  REFERENCES Data.Curcuits(CurcuitID, MaximumCurrent)

 MATCH ON((Data.Devices.CurcuitID = Data.Curcuits.CurcuitID) AND

 (Data.Devices.MaximumCurrent <= Data.Curcuits.MaximumCurrent));

 

However, it is not supported, so I need to use a workaround, one more column and three constraints instead of one, as follows:

 

ALTER TABLE Data.Curcuits

  ADD CONSTRAINT UNQ_Curcuits UNIQUE(CurcuitID, MaximumCurrent);

GO

ALTER TABLE Data.Devices ADD CurcuitMaximumCurrent INT NULL;

GO

ALTER TABLE Data.Devices DROP CONSTRAINT FK_Devices_Curcuits;

GO

ALTER TABLE Data.Devices ADD CONSTRAINT FK_Devices_Curcuits

  FOREIGN KEY(CurcuitID, CurcuitMaximumCurrent)

  REFERENCES Data.Curcuits(CurcuitID, MaximumCurrent)

  ON UPDATE CASCADE;

GO

ALTER TABLE Data.Devices

  ADD CONSTRAINT CHK_Devices_SufficientCurcuitMaximumCurrent

  CHECK(CurcuitMaximumCurrent >= MaximumCurrent);

GO

You can verify that the constraints work:

 

INSERT INTO Data.Devices(DeviceID,

  CurcuitID,

  MaximumCurrent,

  CurcuitMaximumCurrent,

  Description)

SELECT 1, 1, 50, 25, 'Electric car charger'

 

Msg 547, Level 16, State 0, Line 1

The INSERT statement conflicted with the CHECK constraint "CHK_Devices_SufficientCurcuitMaximumCurrent". The conflict occurred in database "Test", table "data.Devices".

The statement has been terminated.

 

 

As you have seen, the implementation of a very simple and very common business rule is quite involved, because such business rules are not directly supported by the database engine.

 

When you want to create constraints on indexed views

 

Even when your database guarantees that “the maximum current of your device cannot exceed the maximum current of the circuit you plug it into”, it is not good enough. Consider the following sample data:

 

INSERT INTO Data.Devices(DeviceID,

  CurcuitID,

  MaximumCurrent,

  CurcuitMaximumCurrent,

  Description)

SELECT 2, 1, 15, 25, 'ShopVac';

 

INSERT INTO Data.Devices(DeviceID,

  CurcuitID,

  MaximumCurrent,

  CurcuitMaximumCurrent,

  Description)

SELECT 3, 1, 15, 25, 'Miter Saw';

 

The database structure allows to plug more than one device into a circuit, which is correct, but if you turn both devices on, their combined maximum current exceeds the circuit’s maximum current. To enforce this business rule, it would be natural to create an indexed view, so that the database guarantees that the totals are always correct:

 

CREATE VIEW Data.TotalMaximumCurrentPerCircuit WITH SCHEMABINDING

AS

SELECT d.CurcuitID,

  c.MaximumCurrent AS CircuitMaximumCurrent,

  SUM(d.MaximumCurrent) AS TotalMaximumCurrent,

  COUNT_BIG(*) AS NumDevices

FROM Data.Devices d JOIN Data.Curcuits c ON d.CurcuitID = c.CurcuitID

GROUP BY d.CurcuitID, c.MaximumCurrent;

GO

CREATE UNIQUE CLUSTERED INDEX Data_TotalMaximumCurrentPerCircuit

ON Data.TotalMaximumCurrentPerCircuit(CurcuitID);

GO

 

If I could create a check constraint on that indexed view, I would be all set:

 

ALTER VIEW Data.TotalMaximumCurrentPerCircuit

  ADD CONSTRAINT CHK_TotalMaximumCurrentPerCircuit_ValidCurcuit

  CHECK(TotalMaximumCurrent <= CircuitMaximumCurrent)

 

 

Instead, I need to use triggers or rather contrived kludges. A built in native support for such quite common business rules would increase the usefulness of SQL Server.

I have not created anything on Connect yet, because the ideas are kind of raw yet. I am very interested in your feedback. What do you think?

 

This continues the series on denormalizing, which started with this post about working hours and appointments 


and continued with this post about running totals.

 

and a post about Storing intervals of time with no overlaps

Using ROWVERSION to enforce business rules

Published Monday, May 04, 2009 11:29 PM by Alexander Kuznetsov

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Andy Irving said:

I think they're under constraints and CREATE ASSERTION in the SQL spec. It would indeed be awesome if SQL Server implemented them properly (no more scalar UDF hackery to implement subquery constraints, for example). I'd prefer if they were implemented like that and not as an extension to foreign keys, but that's just me.

May 5, 2009 4:54 AM
 

Alejandro Mesa said:

Alex,

I agree with Andy. This is another example about the neccesity to implement assertions in SQL Server.

create assertion breaker_on check (

not exists (

select 1

from Data.PlugIn

group by CircuitID

having max(CircuitMaximumLoad) < sum(DeviceCurrent)

)

);

Cheers,

AMB

May 5, 2009 9:15 AM
 

Tweek_cat said:

It would be great. It's very common to have bussiness rules between tables and would be nice to have a direct and clear way to solve it: no more work arrounds.

May 5, 2009 9:46 AM
 

Michael Smith said:

A MATCH ON clause is a great concept! I've always wished for a WHERE (filer) clause so we could limit FK to subtypes.  With filtered indexes, partitioning, and wide tables - seems like a natural evolution.  I hope.  But I've been disappointed with deffered constraints and cyclical cascade action never materializing.

October 27, 2010 7:13 PM

Leave a Comment

(required) 
(required) 
Submit

About Alexander Kuznetsov

Alex Kuznetsov has been working with object oriented languages, mostly C# and C++, as well as with databases for more than a decade. He has worked with Sybase, SQL Server, Oracle and DB2. He regularly blogs on sqlblog.com, mostly about database unit testing, defensive programming, and query optimization. Alex has written a book entitled "Defensive Database Programming with Transact-SQL" and several articles on simple-talk.com and devx.com. Currently he works as an agile developer.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement