Shulou(Shulou.com)05/31 Report--

It is believed that many inexperienced people have no idea about how to achieve constraint enhancement in SQL Server. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

In many cases, it is useful to use more complex logical expressions for foreign keys. In addition, it will be useful to be able to create constraints in indexed views in some cases. I will give an example, and I hope that the voting link for this article will be added as soon as possible. Consider the following simple common sense when more complex logical expressions are needed in foreign keys: the maximum current of your device cannot exceed the maximum current of the circuit you insert into it. Suppose the following table stores circuit and device data: the copy 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, '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: 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 = 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". The 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. As you can see, a very simple and common business rule is also quite complicated to implement, because the database engine does not directly support such business rules. When you want to create constraints on indexed views creates 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: although the database guarantees that "the maximum current of your device cannot exceed the maximum current of the circuit you plug into it," this is not enough. Look at 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: the data in the database indicates that more than one device can be plugged into the circuit, which is not wrong, but when all devices are turned on, the sum of their maximum current exceeds the maximum current of the circuit. To enforce this business rule, it is natural to create an indexed view so that the database ensures that the sum of currents 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 be all set: if I could create a constraint on this indexed view, I would set it like this: ALTER VIEW Data.TotalMaximumCurrentPerCircuit ADD CONSTRAINT CHK_TotalMaximumCurrentPerCircuit_ValidCurcuit CHECK (TotalMaximumCurrent

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.