Search results matching tags 'Data Integrity' and 'Database Programming'

Storing intervals of time with no overlaps.

Alexander Kuznetsov — Mon, 09 Mar 2009 00:12:00 GMT

I will demonstrate how use constraints to make sure that intervals of time have no overlaps. Also you may ensure that there are no gaps either, but if you choose to allow gaps, then the query to retrieve them is very easy. As discussed in my previous posts, you can also use triggers or UDFs wrapped in CHECK constraints, but only trusted constraints can guarantee that all your data is always 100% clean.

Here is the table and the first interval

CREATE TABLE dbo.IntegerSettings(SettingID INT NOT NULL,

IntValue INT NOT NULL,

StartedAt DATETIME NOT NULL,

FinishedAt DATETIME NOT NULL,

PreviousFinishedAt DATETIME NULL,

CONSTRAINT PK_IntegerSettings_SettingID_FinishedAt PRIMARY KEY(SettingID, FinishedAt),

CONSTRAINT UNQ_IntegerSettings_SettingID_PreviousFinishedAt UNIQUE(SettingID, PreviousFinishedAt),

CONSTRAINT FK_IntegerSettings_SettingID_PreviousFinishedAt

FOREIGN KEY(SettingID, PreviousFinishedAt)

REFERENCES dbo.IntegerSettings(SettingID, FinishedAt),

CONSTRAINT CHK_IntegerSettings_PreviousFinishedAt_NotAfter_StartedAt CHECK(PreviousFinishedAt <= StartedAt),

CONSTRAINT CHK_IntegerSettings_StartedAt_Before_FinishedAt CHECK(StartedAt < FinishedAt)

);

INSERT INTO dbo.IntegerSettings(SettingID, IntValue, StartedAt, FinishedAt, PreviousFinishedAt)

VALUES(1, 1, '20070101', '20070103', NULL);

It has five constraints which work together to implement the business rule. Let me demonstrate how the more complex ones work. Of course, some constraints are simple and as such do not need any explanations.

There can be only one first interval for a setting

The constraint UNQ_IntegerSettings_SettingID_PreviousFinishedAt ensures exactly that. The first interval does not have a previous one, which means that PreviousFinishedAt IS NULL. The UNIQUE constraint guarantees that there can be only one such row per setting. See for yourself:

INSERT INTO dbo.IntegerSettings(SettingID, IntValue, StartedAt, FinishedAt, PreviousFinishedAt)

VALUES(1, 1, '20070104', '20070105', NULL);

Server: Msg 2627, Level 14, State 2, Line 1

Violation of UNIQUE KEY constraint 'UNQ_IntegerSettings_SettingID_PreviousFinishedAt'. Cannot insert duplicate key in object 'dbo.IntegerSettings'.

The statement has been terminated.

Next window must begin after the end of the previous one.

The constraint CHK_IntegerSettings_PreviousFinishedAt_NotAfter_StartedAt guarantees exactly that. See for yourself:

INSERT INTO dbo.IntegerSettings(SettingID, IntValue, StartedAt, FinishedAt, PreviousFinishedAt)

VALUES(1, 2, '20070104', '20070109', '20070105')

Server: Msg 547, Level 16, State 1, Line 1

INSERT statement conflicted with TABLE CHECK constraint 'CHK_IntegerSettings_PreviousFinishedAt_NotAfter_StartedAt'. The conflict occurred in database 'RiskCenter', table 'IntegerSettings'.

The statement has been terminated.

Two different windows cannot refer to one and the same window as their previous one.

Again, the same constraint UNQ_IntegerSettings_SettingID_PreviousFinishedAt guarantees precisely that, as demonstrated below:

INSERT INTO dbo.IntegerSettings(SettingID, IntValue, StartedAt, FinishedAt, PreviousFinishedAt)

VALUES(1, 3, '20070104', '20070115', '20070103')

Msg 2627, Level 14, State 1, Line 1

Violation of UNIQUE KEY constraint 'UNQ_IntegerSettings_SettingID_PreviousFinishedAt'. Cannot insert duplicate key in object 'dbo.IntegerSettings'.

The statement has been terminated.

This means that there can be no overlaps.

As you have seen, for every time window, there can be at most one preceding it, and at most one following it. The following interval cannot begin before the end of its previous one. Together these two statements mean that there can be no overlaps.

Working with gaps.

You can prohibit gaps altogether, just replace the following constraint:

CONSTRAINT CHK_IntegerSettings_PreviousFinishedAt_NotAfter_StartedAt CHECK(PreviousFinishedAt <= StartedAt),

With a stricter one, as follows:

CONSTRAINT CHK_IntegerSettings_PreviousFinishedAt_EqualTo_StartedAt CHECK(PreviousFinishedAt = StartedAt),

But if you allow gaps, the query to retrieve them is very simple and performant, as follows:

SELECT PreviousFinishedAt AS GapStart, StartedAt AS GapEnd

FROM dbo.IntegerSettings

WHERE StartedAt > PreviousFinishedAt;

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

and continued with this post about running totals.

Denormalizing to enforce business rules: Running Totals

Alexander Kuznetsov — Fri, 23 Jan 2009 19:57:00 GMT

Calculating running totals is notoriously slow, whether you do it with a cursor or with a triangular join. It is very tempting to denormalize, to store running totals in a column, especially if you select it frequently. However, as usual when you denormalize, you need to guarantee the integrity of your denormalized data. Fortunately, you can guarantee the integrity of running totals with constraints – as long as all your constraints are trusted, all your running totals are correct. Also this way you can easily ensure that the current balance (running totals) is never negative - enforcing by other methods can also be very slow. The following script demonstrates the technique.

CREATE TABLE Data.Inventory(InventoryID INT NOT NULL IDENTITY,

ItemID INT NOT NULL,

ChangeDate DATETIME NOT NULL,

ChangeQty INT NOT NULL,

TotalQty INT NOT NULL,

PreviousChangeDate DATETIME NULL,

PreviousTotalQty INT NULL,

CONSTRAINT PK_Inventory PRIMARY KEY(ItemID, ChangeDate),

CONSTRAINT UNQ_Inventory UNIQUE(ItemID, ChangeDate, TotalQty),

CONSTRAINT UNQ_Inventory_Previous_Columns UNIQUE(ItemID, PreviousChangeDate, PreviousTotalQty),

CONSTRAINT FK_Inventory_Self FOREIGN KEY(ItemID, PreviousChangeDate, PreviousTotalQty)

REFERENCES Data.Inventory(ItemID, ChangeDate, TotalQty),

CONSTRAINT CHK_Inventory_Valid_TotalQty CHECK(TotalQty >= 0 AND (TotalQty = COALESCE(PreviousTotalQty, 0) + ChangeQty)),

CONSTRAINT CHK_Inventory_Valid_Dates_Sequence CHECK(PreviousChangeDate < ChangeDate),

CONSTRAINT CHK_Inventory_Valid_Previous_Columns CHECK((PreviousChangeDate IS NULL AND PreviousTotalQty IS NULL)

OR (PreviousChangeDate IS NOT NULL AND PreviousTotalQty IS NOT NULL))

);

-- beginning of inventory for item 1

INSERT INTO Data.Inventory(ItemID,

ChangeDate,

ChangeQty,

TotalQty,

PreviousChangeDate,

PreviousTotalQty)

VALUES(1, '20090101', 10, 10, NULL, NULL);

-- cannot begin the inventory for the second time for the same item 1

INSERT INTO Data.Inventory(ItemID,

ChangeDate,

ChangeQty,

TotalQty,

PreviousChangeDate,

PreviousTotalQty)

VALUES(1, '20090102', 10, 10, NULL, NULL);

Msg 2627, Level 14, State 1, Line 10

Violation of UNIQUE KEY constraint 'UNQ_Inventory_Previous_Columns'. Cannot insert duplicate key in object 'Data.Inventory'.

The statement has been terminated.

-- add more

DECLARE @ChangeQty INT;

SET @ChangeQty = 5;

INSERT INTO Data.Inventory(ItemID,

ChangeDate,

ChangeQty,

TotalQty,

PreviousChangeDate,

PreviousTotalQty)

SELECT TOP 1 ItemID, '20090103', @ChangeQty, TotalQty + @ChangeQty, ChangeDate, TotalQty

FROM Data.Inventory

WHERE ItemID = 1

ORDER BY ChangeDate DESC;

SET @ChangeQty = 3;

INSERT INTO Data.Inventory(ItemID,

ChangeDate,

ChangeQty,

TotalQty,

PreviousChangeDate,

PreviousTotalQty)

SELECT TOP 1 ItemID, '20090104', @ChangeQty, TotalQty + @ChangeQty, ChangeDate, TotalQty

FROM Data.Inventory

WHERE ItemID = 1

ORDER BY ChangeDate DESC;

SET @ChangeQty = -4;

INSERT INTO Data.Inventory(ItemID,

ChangeDate,

ChangeQty,

TotalQty,

PreviousChangeDate,

PreviousTotalQty)

SELECT TOP 1 ItemID, '20090105', @ChangeQty, TotalQty + @ChangeQty, ChangeDate, TotalQty

FROM Data.Inventory

WHERE ItemID = 1

ORDER BY ChangeDate DESC;

-- try to violate chronological order

SET @ChangeQty = 5;

INSERT INTO Data.Inventory(ItemID,

ChangeDate,

ChangeQty,

TotalQty,

PreviousChangeDate,

PreviousTotalQty)

SELECT TOP 1 ItemID, '20081231', @ChangeQty, TotalQty + @ChangeQty, ChangeDate, TotalQty

FROM Data.Inventory

WHERE ItemID = 1

ORDER BY ChangeDate DESC;

Msg 547, Level 16, State 0, Line 4

The INSERT statement conflicted with the CHECK constraint "CHK_Inventory_Valid_Dates_Sequence". The conflict occurred in database "Test", table "Data.Inventory".

The statement has been terminated.

SELECT ChangeDate,

ChangeQty,

TotalQty,

PreviousChangeDate,

PreviousTotalQty

FROM Data.Inventory ORDER BY ChangeDate;

ChangeDate ChangeQty TotalQty PreviousChangeDate PreviousTotalQty

----------------------- ----------- ----------- ----------------------- -----

2009-01-01 00:00:00.000 10 10 NULL NULL

2009-01-03 00:00:00.000 5 15 2009-01-01 00:00:00.000 10

2009-01-04 00:00:00.000 3 18 2009-01-03 00:00:00.000 15

2009-01-05 00:00:00.000 -4 14 2009-01-04 00:00:00.000 18

-- try to change a single row, all updates must fail

UPDATE Data.Inventory SET ChangeQty = ChangeQty + 2 WHERE InventoryID = 3;

UPDATE Data.Inventory SET TotalQty = TotalQty + 2 WHERE InventoryID = 3;

-- try to delete not the last row, all deletes must fail

DELETE FROM Data.Inventory WHERE InventoryID = 1;

DELETE FROM Data.Inventory WHERE InventoryID = 3;

-- the right way to update

DECLARE @IncreaseQty INT;

SET @IncreaseQty = 2;

UPDATE Data.Inventory SET ChangeQty = ChangeQty + CASE WHEN ItemID = 1 AND ChangeDate = '20090103' THEN @IncreaseQty ELSE 0 END,

TotalQty = TotalQty + @IncreaseQty,

PreviousTotalQty = PreviousTotalQty + CASE WHEN ItemID = 1 AND ChangeDate = '20090103' THEN 0 ELSE @IncreaseQty END

WHERE ItemID = 1 AND ChangeDate >= '20090103';

SELECT ChangeDate,

ChangeQty,

TotalQty,

PreviousChangeDate,

PreviousTotalQty

FROM Data.Inventory ORDER BY ChangeDate;

ChangeDate ChangeQty TotalQty PreviousChangeDate PreviousTotalQty

----------------------- ----------- ----------- ----------------------- ----------------

2009-01-01 00:00:00.000 10 10 NULL NULL

2009-01-03 00:00:00.000 7 17 2009-01-01 00:00:00.000 10

2009-01-04 00:00:00.000 3 20 2009-01-03 00:00:00.000 17

2009-01-05 00:00:00.000 -4 16 2009-01-04 00:00:00.000 20

This continues the series on denormalizing, which started with this post.

Denormalizing to enforce business rules: Part 1.

Alexander Kuznetsov — Wed, 21 Jan 2009 23:49:00 GMT

You cannot use CHECK constraints to compare columns in different rows or in different tables, unless you wrap such functionality in scalar UDFs invoked form a CHECK constraint. What if you actually need to compare columns in different rows or in different tables to enforce a business rule? For example, suppose that you know working hours of a doctor, and you want to make sure that all appointments fit within working hours? Of course, you can use a trigger or a stored procedure to implement this business rule, but neither a trigger nor a stored procedure can give you a 100% guarantee that all your data is clean – someone can disable or drop your trigger, enter some dirty data, and re-enable or recreate your trigger. Also someone can directly modify your table bypassing stored procedures. Either way you can end up with data violating your business rule without knowing about it.

Let me demonstrate how to implement this business rule using only FK and CHECK constraints – that will guarantee that all the data satisfies the business rule as long as all the constraints are trusted.

Note: for more about trusted constraints read a post by SQL Server MVP Hugo Kornelis.

The table storing working hours has one additional constraint which will be used to make sure that all appointments fit within working hours, as follows:

CREATE Table Data.WorkingHours(WorkingHoursID INT NOT NULL,

StartedAt DATETIME NOT NULL,

FinishedAt DATETIME NOT NULL,

ProfessionalsName VARCHAR(50),

CONSTRAINT PK_WorkingHours PRIMARY KEY(WorkingHoursID),

-- The purpose of UNQ_WorkingHours is as follows:

-- a FOREIGN KEY constraint from a child table will refer to it.

CONSTRAINT UNQ_WorkingHours UNIQUE(WorkingHoursID, StartedAt, FinishedAt),

CONSTRAINT CHK_WorkingHours_ValidWindow CHECK(StartedAt < FinishedAt)

);

SET NOCOUNT ON;

INSERT Data.WorkingHours(WorkingHoursID,

ProfessionalsName,

StartedAt,

FinishedAt)

SELECT 1, 'Jane He', '20061025 08:00AM', '20061025 05:00PM' UNION ALL

SELECT 2, 'Jane He', '20061026 08:30AM', '20061026 05:00PM' UNION ALL

SELECT 3, 'Jane He', '20061027 08:30AM', '20061027 05:00PM';

Now I am going to store the beginning and ending of working hours (that is StartedAt and FinishedAt columns) along with every appointment, so that I can use a CHECK constraint to compare them with the beginning and ending of appointments, as follows:

CREATE Table Data.Appointments ( WorkingHoursID INT NOT NULL,

StartedAt DATETIME NOT NULL,

FinishedAt DATETIME NOT NULL,

WorkingHourStartedAt DATETIME NOT NULL,

WorkingHourFinishedAt DATETIME NOT NULL,

CONSTRAINT FK_Appointments_WorkingHours

FOREIGN KEY(WorkingHoursID, WorkingHourStartedAt, WorkingHourFinishedAt)

REFERENCES Data.WorkingHours(WorkingHoursID, StartedAt, FinishedAt) ON UPDATE CASCADE,

CONSTRAINT CHK_Appointments_ValidWindow CHECK(StartedAt < FinishedAt),

CONSTRAINT CHK_TimesInRange

CHECK((StartedAt BETWEEN WorkingHourStartedAt AND WorkingHourFinishedAt)

AND (FinishedAt BETWEEN WorkingHourStartedAt AND WorkingHourFinishedAt))

);

Clearly storing two columns from the parent table in the child one is denormalization. However, my FOREIGN KEY constraint guarantees that the columns from the parent table always match the corresponding ones in the child one. On top of that, the ON UPDATE CASCADE clause allows me to modify the values in the parent table, and these changes are propagated into the child one automatically. I will demonstrate it soon. The following inserts succeed, because the appointments fit into business hours:

INSERT Data.Appointments(WorkingHoursID,

StartedAt,

FinishedAt,

WorkingHourStartedAt,

WorkingHourFinishedAt)

SELECT 1, '20061025 09:00AM', '20061025 05:00PM', '20061025 08:00AM', '20061025 05:00PM' UNION ALL

SELECT 2, '20061026 08:30AM', '20061026 01:00PM', '20061026 08:30AM', '20061026 05:00PM' UNION ALL

SELECT 3, '20061027 10:30AM', '20061027 01:00PM', '20061027 08:30AM', '20061027 05:00PM';

You can change appointments and working hours as long as appointments still fit, for example:

UPDATE Data.Appointments SET StartedAt = '20061025 08:30AM'

WHERE WorkingHoursID = 1;

UPDATE Data.WorkingHours SET StartedAt = '20061027 09:31AM'

WHERE WorkingHoursID = 3;

UPDATE Data.WorkingHours SET FinishedAt = '20061026 04:31PM'

WHERE WorkingHoursID = 2;

Note that the changes in WorkingHours table were propagated into the child table automatically:

SELECT StartedAt,

FinishedAt,

WorkingHourStartedAt,

WorkingHourFinishedAt

FROM Data.Appointments;

StartedAt FinishedAt WorkingHourStartedAt WorkingHourFinishedAt

----------------------- ----------------------- ----------------------- -----------------------

2006-10-25 08:30:00.000 2006-10-25 17:00:00.000 2006-10-25 08:00:00.000 2006-10-25 17:00:00.000

2006-10-26 08:30:00.000 2006-10-26 13:00:00.000 2006-10-26 08:30:00.000 2006-10-26 16:31:00.000

2006-10-27 10:30:00.000 2006-10-27 13:00:00.000 2006-10-27 09:31:00.000 2006-10-27 17:00:00.000

However, you cannot extend your appointment beyond working hour nor you can shorten your working hours so that some appointment no longer fits:

-- all fail: range not completely inside the parent range

INSERT Data.Appointments VALUES(3, '20061027 7:30AM', '20061027 01:00PM', '20061027 08:30AM', '20061027 05:00PM');

INSERT Data.Appointments VALUES(3, '20061027 10:30AM', '20061027 08:00PM', '20061027 08:30AM', '20061027 05:00PM');

-- fails because there is an appoinment starting at 10:30

UPDATE Data.WorkingHours SET StartedAt = '20061027 10:31AM' WHERE WorkingHoursID = 3;

Msg 547, Level 16, State 0, Line 2

The INSERT statement conflicted with the CHECK constraint "CHK_TimesInRange". The conflict occurred in database "Test", table "Data.Appointments".

The statement has been terminated.

Msg 547, Level 16, State 0, Line 3

The INSERT statement conflicted with the CHECK constraint "CHK_TimesInRange". The conflict occurred in database "Test", table "Data.Appointments".

The statement has been terminated.

Msg 547, Level 16, State 0, Line 5

The UPDATE statement conflicted with the CHECK constraint "CHK_TimesInRange". The conflict occurred in database "Test", table "Data.Appointments".

The statement has been terminated.

In many cases constraints also perform better than triggers, but this is another story…

In the next post I am discussing running totals