Two suggestions for SQL Server constraint Enhancement

In many cases, it is very useful to use more complex logical expressions for external keys. In addition, in some cases, it is very practical to create constraints on the index view. I will give examples and hope that the voting link for this article will be added as soon as possible.
When a more complex logical expression is required for an external key
Consider the following simple knowledge: the maximum current of your device cannot exceed the maximum current of the circuit you have inserted into it. Assume that the following table stores circuit and device data:
Copy codeThe Code is as follows:
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, 'ck 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 wocould be very convenient to issue a simple command and implement this business rule:
A simple command may 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:
However, this statement is not supported, so you must use another method -- add one more column constraint and use three instead of one constraint, as shown below:
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:
You can verify that the constraint is valid:
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.
The INSERT statement conflicts with the CHECK constraint "CHK_Devices_SufficientCurcuitMaximumCurrent. This conflict occurs in the "data. Devices" table of the Database "Test.
The statement is 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.
It can be seen that a very simple and common business rule is also complicated to implement, because the database engine does not directly support such business rules.
When you want to create constraints on indexed views
Create constraints on the index View
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:
Although the database ensures that "the maximum current of your device cannot exceed the maximum current of the circuit you inserted into it", this is not enough. See 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 wocould be natural to create an indexed view, so that the database guarantees that the totals are always correct:
The data in the database indicates that more than one device can be inserted into the circuit, which is correct. However, when all the devices are turned on, their maximum current may exceed the maximum current of the circuit. To strengthen this business rule, it is natural to create an index view to ensure that the sum of current in the database 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 cocould create a check constraint on that indexed view, I wocould be all set:
If you can create a constraint on this index view, I will make the following settings:
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 wocould increase the usefulness of SQL Server.
In fact, I must use a trigger or carefully pieced together the Check constraints. If the database has built-in support for such common business rules, it will increase the practicality of SQL Server.