Child Intervals Of Time Inside Parent Ones - Enforced By RI

I needed to enforce a business rule that appointments fit into shifts.
The only one watertight way of doing it that i know about is to use RI, as follows:

CREATE Table Shifts(ShiftID INT NOT NULL PRIMARY KEY,
StartedAt DATETIME NOT NULL,
FinishedAt DATETIME NOT NULL,
CONSTRAINT UK_Shifts UNIQUE(ShiftID, StartedAt, FinishedAt))
go
SET NOCOUNT ON
INSERT Shifts VALUES(1, '20061025 08:00AM', '20061025 05:00PM')
INSERT Shifts VALUES(2, '20061026 08:30AM', '20061026 05:00PM')
INSERT Shifts VALUES(3, '20061027 08:30AM', '20061027 05:00PM')
go
CREATE Table Appointments ( ShiftID INT NOT NULL,
StartedAt DATETIME NOT NULL,
FinishedAt DATETIME NOT NULL,
ShiftStartedAt DATETIME NOT NULL,
ShiftFinishedAt DATETIME NOT NULL,
CONSTRAINT FK_Appointments FOREIGN KEY(ShiftID, ShiftStartedAt, ShiftFinishedAt)
REFERENCES Shifts(ShiftID, StartedAt, FinishedAt) ON UPDATE CASCADE,
CONSTRAINT CHK_TimesInRange CHECK((StartedAt BETWEEN ShiftStartedAt AND
ShiftFinishedAt) AND (FinishedAt BETWEEN ShiftStartedAt AND ShiftFinishedAt)))
go
INSERT Appointments VALUES(1, '20061025 09:00AM', '20061025 05:00PM', '20061025
08:00AM', '20061025 05:00PM')
INSERT Appointments VALUES(2, '20061026 08:30AM', '20061026 01:00PM', '20061026
08:30AM', '20061026 05:00PM')
INSERT Appointments VALUES(3, '20061027 10:30AM', '20061027 01:00PM', '20061027
08:30AM', '20061027 05:00PM')
---- these updates succeed: appointments still fit into their shifts
UPDATE Shifts SET StartedAt = '20061027 09:31AM' WHERE ShiftID = 3
UPDATE Shifts SET FinishedAt = '20061027 04:31PM' WHERE ShiftID = 2
go
-- all fail: range not completely inside the parent range
INSERT Appointments VALUES(3, '20061027 7:30AM', '20061027 01:00PM', '20061027
08:30AM', '20061027 05:00PM')
INSERT 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 Shifts SET StartedAt = '20061027 10:31AM' WHERE ShiftID = 3

Think ANSI Standard SQL Is Fully Portable Between Databases? Think Again.

My next article went live on devx.com:

Think ANSI Standard SQL Is Fully Portable Between Databases? Think Again.

The same ANSI Standard SQL query can run against identical data on SQL Server and Oracle and return different results. So just because your application issues ANSI standard SQL, don't assume it's portable.

http://www.devx.com/dbzone/Article/32852

Implementing One-To-One Relationship.

Suppose you have two tables, as follows:

CREATE TABLE LeftHalf(PairID INT NOT NULL PRIMARY KEY,
SomeData VARCHAR(20))
go
CREATE TABLE RightHalf(PairID INT NOT NULL PRIMARY KEY,
SomeData VARCHAR(20))
go
INSERT LeftHalf(PairID, SomeData) VALUES(1, 'Left Half 1')
INSERT RightHalf(PairID, SomeData) VALUES(1, 'Right Half 1')
go

Assume you want to enforse one-to-one relationship between them:

ALTER TABLE LeftHalf ADD CONSTRAINT LeftHalfHasMatchingRight FOREIGN KEY(PairID) REFERENCES RightHalf
go
ALTER TABLE RightHalf ADD CONSTRAINT RightHalfHasMatchingLeft FOREIGN KEY(PairID) REFERENCES LeftHalf
go

Unfortunately, this straightforward approach does not allow you to add new rows:

INSERT LeftHalf(PairID, SomeData) VALUES(2, 'Some Data')
/*
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "LeftHalfHasMatchingRight". The conflict occurred in database "Sandbox", table "dbo.RightHalf", column 'PairID'.
The statement has been terminated.
*/

Let us implement deferrable constraints instead:

ALTER TABLE LeftHalf DROP CONSTRAINT LeftHalfHasMatchingRight
go
ALTER TABLE RightHalf DROP CONSTRAINT RightHalfHasMatchingLeft
go

ALTER TABLE LeftHalf ADD InTransientState CHAR(1) CHECK(InTransientState IN('Y', 'N'))
go
ALTER TABLE RightHalf ADD InTransientState CHAR(1) CHECK(InTransientState IN('Y', 'N'))
go
ALTER TABLE LeftHalf ADD MatchingRight AS CASE WHEN InTransientState = 'N' THEN PairID END PERSISTED
go
ALTER TABLE RightHalf ADD MatchingLeft AS CASE WHEN InTransientState = 'N' THEN PairID END PERSISTED
go
ALTER TABLE LeftHalf ADD CONSTRAINT LeftHalfHasMatchingRight FOREIGN KEY(MatchingRight) REFERENCES RightHalf
go
ALTER TABLE RightHalf ADD CONSTRAINT RightHalfHasMatchingLeft FOREIGN KEY(MatchingLeft) REFERENCES LeftHalf
go
UPDATE LeftHalf SET InTransientState = 'N'
UPDATE RightHalf SET InTransientState = 'N'
go

Now you can add rows all right as long as you defer RI checking until both rows in a pair are added, as follows:

BEGIN TRAN
INSERT LeftHalf(PairID, SomeData, InTransientState) VALUES(2, 'Left Half 2', 'Y')
INSERT RightHalf(PairID, SomeData, InTransientState) VALUES(2, 'Right Half 2', 'Y')
UPDATE LeftHalf SET InTransientState = 'N' WHERE PairID = 2
UPDATE RightHalf SET InTransientState = 'N' WHERE PairID = 2
COMMIT
go

Note that straightforward insert will not work, as before:

INSERT LeftHalf(PairID, SomeData, InTransientState) VALUES(3, 'Left Half 3', 'N')
INSERT RightHalf(PairID, SomeData, InTransientState) VALUES(3, 'Right Half 3', 'N')

Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "LeftHalfHasMatchingRight". The conflict occurred in database "Sandbox", table "dbo.RightHalf", column 'PairID'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 2
The INSERT statement conflicted with the FOREIGN KEY constraint "RightHalfHasMatchingLeft". The conflict occurred in database "Sandbox", table "dbo.LeftHalf", column 'PairID'.
The statement has been terminated.

But the data not in transient state is guaranteed to be consistent:

SELECT * FROM LeftHalf WHERE InTransientState = 'N'
SELECT * FROM RightHalf WHERE InTransientState = 'N'
/*
PairID SomeData InTransientState MatchingRight
----------- -------------------- ---------------- -------------
1 Left Half 1 N 1
2 Left Half 2 N 2

(2 row(s) affected)

PairID SomeData InTransientState MatchingLeft
----------- -------------------- ---------------- ------------
1 Right Half 1 N 1
2 Right Half 2 N 2

Mimicking Deferrable Constraints With Persisted Computed Columns.

In Oracle you can utilize deferrable constraints (when RI checks are delayed until commit time) - this is very convenient in some cases.
In SQL Server deferrable constraints are not supported, but you can mimick this behaviour using persisted computed columns.
For instance, suppose two columns in your child table reference one and the same primary key as follows:

CREATE TABLE Employee(EmployeeName VARCHAR(30) NOT NULL PRIMARY KEY, Extension CHAR(4))
go
CREATE TABLE Task(CompletedBy VARCHAR(30) NOT NULL, ReviewedBy VARCHAR(30) NULL, Description VARCHAR(30))
go
ALTER TABLE Task ADD CONSTRAINT TaskCompletedFK FOREIGN KEY(CompletedBy) REFERENCES Employee(EmployeeName)
go
ALTER TABLE Task ADD CONSTRAINT TaskReviewedByFK FOREIGN KEY(ReviewedBy) REFERENCES Employee(EmployeeName)
go

Also suppose that your primary key is not immutable and you need to cascade the changes in the parent's primary key down to the child table

ALTER TABLE Task DROP CONSTRAINT TaskCompletedFK
go
ALTER TABLE Task DROP CONSTRAINT TaskReviewedByFK
go

You can create one foreign key with cascading updates all right:

ALTER TABLE Task ADD CONSTRAINT TaskCompletedFK FOREIGN KEY(CompletedBy) REFERENCES Employee(EmployeeID)
ON UPDATE CASCADE
go

But the second constraint cannot be not created with ON UPDATE CASCADE ON DELETE CASCADE option:

ALTER TABLE Task ADD CONSTRAINT TaskReviewedByFK FOREIGN KEY(ReviewedBy) REFERENCES Employee(EmployeeID)
ON UPDATE CASCADE
go

Msg 1785, Level 16, State 0, Line 2
Introducing FOREIGN KEY constraint 'TaskReviewedByFK' on table 'Task' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.

You have no choice but implement cascading updates manually.
Note that dealing with mutating primary keys in triggers is very inconvenient to put it mildly. However, you can add a couple of columns to your table and easily cascade the changes without troubling yourself too terribly much. Add two columns and a constraint as follows:

ALTER TABLE Task ADD InTransientState CHAR(1) CHECK(InTransientState IN('Y', 'N'))
go
ALTER TABLE Task ADD ReviewedByNotTransient AS CASE WHEN InTransientState = 'N' THEN ReviewedBy END PERSISTED
go
ALTER TABLE Task ADD CONSTRAINT TaskReviewedByFK FOREIGN KEY(ReviewedByNotTransient) REFERENCES Employee(EmployeeName)
go

Let us populate the tables

INSERT Employee VALUES('Doe, John', '1234')
INSERT Employee VALUES('Doe, Jane', '1235')
INSERT Employee VALUES('Jill, Ruben', '1236')
go
INSERT Task(CompletedBy, ReviewedBy, InTransientState) VALUES('Doe, John', 'Doe, Jane', 'N')
INSERT Task(CompletedBy, ReviewedBy, InTransientState) VALUES('Doe, Jane', 'Jill, Ruben', 'N')
go

Note that a straightforward UPDATE will not work, because it is not cascaded down to ReviewedBy column:

UPDATE Employee SET EmployeeName = 'Doe, Janette' WHERE EmployeeName = 'Doe, Jane'
/*
Msg 547, Level 16, State 0, Line 1
The UPDATE statement conflicted with the REFERENCE constraint "TaskReviewedByFK". The conflict occurred in database "Sandbox", table "dbo.Task", column 'ReviewedByNotTransient'.
The statement has been terminated.
*/

But a slightly more complex script will work all right (error handling skipped for brevity):

BEGIN TRAN
--- Turn off TaskReviewedByFK temporarily for Doe, Jane only
UPDATE Task SET InTransientState = 'Y' WHERE ReviewedBy = 'Doe, Jane'
--- update the PK
UPDATE Employee SET EmployeeName = 'Doe, Janette' WHERE EmployeeName = 'Doe, Jane'
--- change the FK accordingly and get the rows back into RI scope
UPDATE Task SET ReviewedBy = 'Doe, Janette', InTransientState = 'N' WHERE ReviewedBy = 'Doe, Jane'
COMMIT
-- verify that it worked. Note that changes to CompletedBy cascaded automatically.
SELECT * FROM Task

As you have seen, a persisted computed column allowed you to postpone RI verification.