In many cases, it is useful to use more complex logical expressions for foreign keys. In addition, it is also useful in some cases to be able to create constraints in an indexed view. I will give an example and I hope that the voting link for this article will be added as soon as possible.
When a more complex logical expression is required in a foreign key
Consider the following simple common sense: Your device's maximum current cannot exceed the maximum current of the circuit you are inserting into it. Suppose the following table stores circuit and device data:
Copy Code code as follows:
CREATE TABLE data.curcuits (curcuitid INT not NULL
CONSTRAINT pk_curcuits PRIMARY KEY,
Maximumcurrent INT not NULL,
Description VARCHAR (MB) not NULL);
Go
INSERT into Data.curcuits (Curcuitid,
Maximumcurrent,
Description)
SELECT 1, ' 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 (m) 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:
A very simple command might 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 isn't supported, so I need to use a workaround, one more column and three constraints of one, as instead oWS
However, the statement is not supported, so there must be another way-to add more than one column of constraints, using 3 instead of 1 constraints, 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
Can verify that the constraints work:
You can verify that the constraint is valid:
INSERT into Data.devices (DeviceID,
Curcuitid,
Maximumcurrent,
Curcuitmaximumcurrent,
Description)
SELECT 1, 1,, ' electric car charger '
MSG 547, level, 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.
The INSERT statement and check constraint "chk_devices_sufficientcurcuitmaximumcurrent" conflict. The conflict occurred in the database "Test" data. Devices "table.
The statement was terminated for execution.
As you have seen, the implementation of a very simple and very common business the rule is quite involved, because such busine SS rules are not directly supported by the database engine.
As you can see, a very simple and common business rule is quite complex to implement because the database engine does not directly support this business rule.
When you are want to create constraints on indexed views
Create a constraint on an indexed view
Even when your database guarantees this maximum current of your device the cannot exceed the maximum It's plug it into, it's not good enough. Consider the following sample data:
Although the database guarantees that "the maximum current of your device cannot exceed the maximum current of the circuit you plugged into it", this is not enough. Take a look at the following sample data:
INSERT into Data.devices (DeviceID,
Curcuitid,
Maximumcurrent,
Curcuitmaximumcurrent,
Description)
SELECT 2, 1, MB, ' Shopvac ';
INSERT into Data.devices (DeviceID,
Curcuitid,
Maximumcurrent,
Curcuitmaximumcurrent,
Description)
SELECT 3, 1, miter, ' Saw ';
The database structure allows to plug more than one device into a circuit, which are correct, but if you turn both On, their combined maximum current exceeds the circuit ' s maximum. To enforce this business rule, it would is natural to create a indexed view, so of the database guarantees that the TOT ALS are always correct:
The data in the database indicates that it is possible to insert more than one device into the circuit, which is not wrong, but when all devices are turned on, their maximum current will exceed the maximum current of the circuit. To enforce this business rule, it is natural to create an indexed view so that the database guarantees that the current is 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 is all set:
If you can create a constraint on this indexed view, I will make this setting:
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 the increase of SQL Server.
In fact, I have to use triggers or a patchwork of check constraints to implement them. If the database has built-in support for this fairly common business rule, it will increase the usefulness of SQL Server.
