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