Spilitting databases and maintaining foreign key relationships

Database are becoming less monolithic and sometimes it is convenient to split a large database into smaller ones. The idea is to abstract the data and to ease the deployment of large amounts of data. To make this work you need to be able to in-force foreign key relationships so as to preserve the integrity of the data. Care needs to be taken concerning queries that cross one database to another. The reason is that SQL Server has a sql compiler that learns about the content of the database and uses this to optimize the execution plan of queries. Provided there are no cross cutting queries who’s performance have been effected the following script is a good way of enforcing cross database foreign key relationships.

USE [master]
GO

CREATE DATABASE [Database] ON  PRIMARY
( NAME = N'PrimaryFileName', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Database.mdf' , SIZE = 6336KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'PrimaryLogFileName', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Database_log.ldf' , SIZE = 10176KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

CREATE DATABASE [DatabaseCopy] ON  PRIMARY
( NAME = N'PrimaryFileName', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\DatabaseCopy.mdf' , SIZE = 6336KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'PrimaryLogFileName', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\DatabaseCopy_log.ldf' , SIZE = 10176KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

-- CREATE DatabaseLink (Linked Server), only necessary if the databases are on different servers

USE [Database]
GO

CREATE TABLE MainTab
(
    Id UNIQUEIDENTIFIER PRIMARY KEY,
    ValueA INTEGER,
    ValueB VARCHAR(50)
);

USE [DatabaseCopy]
GO

CREATE TABLE MainTab
(
    Id UNIQUEIDENTIFIER PRIMARY KEY,
);

CREATE TABLE ChildTab
(
    Id UNIQUEIDENTIFIER PRIMARY KEY,
    Parent UNIQUEIDENTIFIER REFERENCES MainTab ON DELETE CASCADE,
    ValueA INTEGER
);

-- Create Triggers on the "Master" Database
USE [Database]
GO

CREATE TRIGGER [trg_InsertParentRec]
    ON  MainTab
    AFTER INSERT
AS
BEGIN
    /*Name of linked server shall be added here*/
    INSERT INTO [DatabaseCopy].[dbo].[MainTab](Id) (SELECT Id FROM inserted);
END
GO

CREATE TRIGGER [trg_DeleteParentRec]
    ON  MainTab
    AFTER DELETE
AS
BEGIN
    /*Name of linked server shall be added here*/
    DELETE FROM [DatabaseCopy].[dbo].[MainTab] WHERE Id  IN (SELECT deleted.Id FROM deleted);
END
GO

-- Insert into Master Database
USE [Database]
GO

INSERT INTO MainTab(Id, ValueA, ValueB) VALUES('5D72B397-A066-43A4-B409-117DED69C41E', 10, 'AAA');
INSERT INTO MainTab(Id, ValueA, ValueB) VALUES('BA9403BE-A7B2-4209-807E-2F90F04A1B72', 20, 'BBB');
INSERT INTO MainTab(Id, ValueA, ValueB) VALUES('4A6283CB-4C63-4F6B-8D16-AFB4D36E4036', 30, 'CCC');
INSERT INTO MainTab(Id, ValueA, ValueB) VALUES('F268C4DA-7A88-49D2-B423-E15622D9BC00', 40, 'DDD');

SELECT * FROM MainTab;

-- The Records in the MainTab should be automatically created by the trigger
-- Add some child records
USE [DatabaseCopy]
GO

SELECT * FROM MainTab;

INSERT INTO ChildTab(Id, Parent, ValueA) VALUES(NEWID(), '5D72B397-A066-43A4-B409-117DED69C41E', 100);
INSERT INTO ChildTab(Id, Parent, ValueA) VALUES(NEWID(), '5D72B397-A066-43A4-B409-117DED69C41E', 200);
INSERT INTO ChildTab(Id, Parent, ValueA) VALUES(NEWID(), 'BA9403BE-A7B2-4209-807E-2F90F04A1B72', 300);
INSERT INTO ChildTab(Id, Parent, ValueA) VALUES(NEWID(), 'BA9403BE-A7B2-4209-807E-2F90F04A1B72', 400);
INSERT INTO ChildTab(Id, Parent, ValueA) VALUES(NEWID(), '4A6283CB-4C63-4F6B-8D16-AFB4D36E4036', 500);
INSERT INTO ChildTab(Id, Parent, ValueA) VALUES(NEWID(), 'F268C4DA-7A88-49D2-B423-E15622D9BC00', 600);

SELECT * FROM ChildTab;

-- Delete a record from the Master Database
USE [Database]
GO

DELETE FROM MainTab WHERE Id = '5D72B397-A066-43A4-B409-117DED69C41E';

SELECT * FROM MainTab;

-- Also the Main and the child record should be deleted
USE [DatabaseCopy]
GO

SELECT * FROM MainTab;
SELECT * FROM ChildTab;