Here is what I’ve tested:
```
/====================================================
Two schemas (A, B) each with a 1-to-many relationship:
A.C (1) -> A.D (many)
B.C (1) -> B.E (many)
====================================================/
SET NOCOUNT ON;
GO
/*====================================================
- Create Schemas
====================================================*/
IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = 'A')
EXEC('CREATE SCHEMA A');
GO
IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = 'B')
EXEC('CREATE SCHEMA B');
GO
/====================================================
2) Drop tables (child first, then parent)
====================================================/
IF OBJECT_ID('A.D', 'U') IS NOT NULL DROP TABLE A.D;
IF OBJECT_ID('A.C', 'U') IS NOT NULL DROP TABLE A.C;
IF OBJECT_ID('B.E', 'U') IS NOT NULL DROP TABLE B.E;
IF OBJECT_ID('B.C', 'U') IS NOT NULL DROP TABLE B.C;
GO
/====================================================
3) Create tables for Schema A (A.C -> A.D)
====================================================/
CREATE TABLE A.C
(
CId INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_A_C PRIMARY KEY,
Name NVARCHAR(100) NOT NULL,
CreatedDate DATETIME2 NOT NULL CONSTRAINT DF_A_C_CreatedDate DEFAULT SYSDATETIME()
);
CREATE TABLE A.D
(
DId INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_A_D PRIMARY KEY,
CId INT NOT NULL, -- FK to A.C (one C has many Ds)
Description NVARCHAR(200) NOT NULL,
Amount DECIMAL(10,2) NOT NULL,
CreatedDate DATETIME2 NOT NULL CONSTRAINT DF_A_D_CreatedDate DEFAULT SYSDATETIME(),
CONSTRAINT FK_A_D_A_C
FOREIGN KEY (CId) REFERENCES A.C(CId)
ON DELETE CASCADE
ON UPDATE NO ACTION
);
CREATE INDEX IX_A_D_CId ON A.D(CId);
GO
/====================================================
4) Create tables for Schema B (B.C -> B.E)
====================================================/
CREATE TABLE B.C
(
CId INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_B_C PRIMARY KEY,
Title NVARCHAR(100) NOT NULL,
IsActive BIT NOT NULL CONSTRAINT DF_B_C_IsActive DEFAULT (1)
);
CREATE TABLE B.E
(
EId INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_B_E PRIMARY KEY,
CId INT NOT NULL, -- FK to B.C (one C has many Es)
Code NVARCHAR(50) NOT NULL,
CreatedOn DATE NOT NULL,
CONSTRAINT FK_B_E_B_C
FOREIGN KEY (CId) REFERENCES B.C(CId)
ON DELETE CASCADE
ON UPDATE NO ACTION
);
CREATE INDEX IX_B_E_CId ON B.E(CId);
GO
/====================================================
5) Populate Schema A (parents first, then children)
====================================================/
INSERT INTO A.C (Name)
VALUES ('Alpha'), ('Beta'), ('Gamma');
-- Each A.C gets multiple A.D rows (1-to-many)
INSERT INTO A.D (CId, Description, Amount)
VALUES
(1, 'Alpha - line 1', 10.00),
(1, 'Alpha - line 2', 25.50),
(2, 'Beta - line 1', 99.99),
(2, 'Beta - line 2', 15.00),
(2, 'Beta - line 3', 5.25),
(3, 'Gamma - line 1', 42.42);
GO
/====================================================
6) Populate Schema B (parents first, then children)
====================================================/
INSERT INTO B.C (Title, IsActive)
VALUES
('Item One', 1),
('Item Two', 0),
('Item Three', 1);
-- Each B.C gets multiple B.E rows (1-to-many)
INSERT INTO B.E (CId, Code, CreatedOn)
VALUES
(1, 'ONE-001', '2024-01-01'),
(1, 'ONE-002', '2024-01-15'),
(2, 'TWO-001', '2024-02-01'),
(3, 'THR-001', '2024-03-10'),
(3, 'THR-002', '2024-03-11'),
(3, 'THR-003', '2024-03-12');
GO
/====================================================
7) Verification (optional)
====================================================/
SELECT 'A.C' AS TableName, * FROM A.C ORDER BY CId;
SELECT 'A.D' AS TableName, * FROM A.D ORDER BY CId, DId;
SELECT 'B.C' AS TableName, * FROM B.C ORDER BY CId;
SELECT 'B.E' AS TableName, * FROM B.E ORDER BY CId, EId;
GO
```
