SQL Server / Azure SQL
Intelli Onboard Table Creation
Idempotent creation script for the tenant-aware Student and StudentDocument tables, including foreign keys, uniqueness rules, and lookup indexes.
Checks before table creation
Safe to run repeatedly: The script checks that the shared
Tenant and UserExtended tables exist, checks each new table with OBJECT_ID, and checks every index through sys.indexes before creating it. Existing tables and data are not dropped or overwritten.Complete query
/*
Intelli Onboard - SQL Server / Azure SQL schema
Safe to re-run: every table and index is checked before creation.
Requires existing dbo.Tenant(Id) and dbo.UserExtended(Id).
*/
SET XACT_ABORT ON;
GO
IF OBJECT_ID(N'dbo.Tenant', N'U') IS NULL
THROW 50001, 'Required table dbo.Tenant does not exist. Create the shared Tenant table first.', 1;
GO
IF OBJECT_ID(N'dbo.UserExtended', N'U') IS NULL
THROW 50002, 'Required table dbo.UserExtended does not exist. Create the shared UserExtended table first.', 1;
GO
/* 1. Student: one-to-one with UserExtended */
IF OBJECT_ID(N'dbo.Student', N'U') IS NULL
BEGIN
CREATE TABLE dbo.Student
(
UserExtendedId BIGINT NOT NULL,
TenantId BIGINT NOT NULL,
UIDNumber NVARCHAR(100) NULL,
FatherName NVARCHAR(200) NULL,
MotherName NVARCHAR(200) NULL,
CreatedBy BIGINT NULL,
CreatedOn DATETIME2(3) NOT NULL
CONSTRAINT DF_Student_CreatedOn DEFAULT SYSUTCDATETIME(),
UpdatedBy BIGINT NULL,
UpdatedOn DATETIME2(3) NULL,
IsActive BIT NOT NULL
CONSTRAINT DF_Student_IsActive DEFAULT (1),
CONSTRAINT PK_Student PRIMARY KEY CLUSTERED (UserExtendedId),
CONSTRAINT FK_Student_UserExtended
FOREIGN KEY (UserExtendedId) REFERENCES dbo.UserExtended(Id),
CONSTRAINT FK_Student_Tenant
FOREIGN KEY (TenantId) REFERENCES dbo.Tenant(Id),
CONSTRAINT FK_Student_CreatedBy
FOREIGN KEY (CreatedBy) REFERENCES dbo.UserExtended(Id),
CONSTRAINT FK_Student_UpdatedBy
FOREIGN KEY (UpdatedBy) REFERENCES dbo.UserExtended(Id)
);
END;
GO
IF OBJECT_ID(N'dbo.Student', N'U') IS NOT NULL
AND NOT EXISTS
(
SELECT 1 FROM sys.indexes
WHERE object_id = OBJECT_ID(N'dbo.Student')
AND name = N'IX_Student_TenantId'
)
BEGIN
CREATE INDEX IX_Student_TenantId
ON dbo.Student(TenantId, UserExtendedId);
END;
GO
IF OBJECT_ID(N'dbo.Student', N'U') IS NOT NULL
AND NOT EXISTS
(
SELECT 1 FROM sys.indexes
WHERE object_id = OBJECT_ID(N'dbo.Student')
AND name = N'UX_Student_TenantId_UIDNumber'
)
BEGIN
CREATE UNIQUE INDEX UX_Student_TenantId_UIDNumber
ON dbo.Student(TenantId, UIDNumber)
WHERE UIDNumber IS NOT NULL;
END;
GO
/* 2. StudentDocument: metadata for files stored in a private Blob container */
IF OBJECT_ID(N'dbo.StudentDocument', N'U') IS NULL
BEGIN
CREATE TABLE dbo.StudentDocument
(
Id BIGINT IDENTITY(1,1) NOT NULL,
TenantId BIGINT NOT NULL,
UserExtendedId BIGINT NOT NULL,
DocumentName NVARCHAR(500) NOT NULL,
FileSize BIGINT NOT NULL,
FileType NVARCHAR(150) NOT NULL,
Extension NVARCHAR(30) NOT NULL,
FilePath NVARCHAR(1000) NOT NULL,
CreatedBy BIGINT NULL,
CreatedOn DATETIME2(3) NOT NULL
CONSTRAINT DF_StudentDocument_CreatedOn DEFAULT SYSUTCDATETIME(),
UpdatedBy BIGINT NULL,
UpdatedOn DATETIME2(3) NULL,
IsActive BIT NOT NULL
CONSTRAINT DF_StudentDocument_IsActive DEFAULT (1),
CONSTRAINT PK_StudentDocument PRIMARY KEY CLUSTERED (Id),
CONSTRAINT FK_StudentDocument_Student
FOREIGN KEY (UserExtendedId) REFERENCES dbo.Student(UserExtendedId),
CONSTRAINT FK_StudentDocument_Tenant
FOREIGN KEY (TenantId) REFERENCES dbo.Tenant(Id),
CONSTRAINT FK_StudentDocument_CreatedBy
FOREIGN KEY (CreatedBy) REFERENCES dbo.UserExtended(Id),
CONSTRAINT FK_StudentDocument_UpdatedBy
FOREIGN KEY (UpdatedBy) REFERENCES dbo.UserExtended(Id),
CONSTRAINT CK_StudentDocument_FileSize
CHECK (FileSize >= 0)
);
END;
GO
IF OBJECT_ID(N'dbo.StudentDocument', N'U') IS NOT NULL
AND NOT EXISTS
(
SELECT 1 FROM sys.indexes
WHERE object_id = OBJECT_ID(N'dbo.StudentDocument')
AND name = N'IX_StudentDocument_Tenant_Student'
)
BEGIN
CREATE INDEX IX_StudentDocument_Tenant_Student
ON dbo.StudentDocument(TenantId, UserExtendedId, IsActive);
END;
GO
IF OBJECT_ID(N'dbo.StudentDocument', N'U') IS NOT NULL
AND NOT EXISTS
(
SELECT 1 FROM sys.indexes
WHERE object_id = OBJECT_ID(N'dbo.StudentDocument')
AND name = N'UX_StudentDocument_FilePath'
)
BEGIN
CREATE UNIQUE INDEX UX_StudentDocument_FilePath
ON dbo.StudentDocument(FilePath);
END;
GO
PRINT 'Intelli Onboard tables and indexes are ready.';
GO