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