SQL Server 建庫、建表、約束管理 T-SQL 規範腳本模板

規範的 T-SQL 腳本是版本控制、自動化部署、災難恢復的基石。本文提供一套生產級模板：冪等建庫（存在則重建）、文件組規劃、表創建與約束分離、外鍵順序控制、約束命名規範，以「學校教務系統」為例完整演示。

一、 規範建庫：冪等 + 文件組分離

-- 1. 冪等刪除重建
IF EXISTS (SELECT * FROM sys.databases WHERE name = 'School')
    DROP DATABASE School;
GO

CREATE DATABASE School
ON PRIMARY (                                     -- 主文件組：系統/元數據
    NAME = 'School_Data',
    FILENAME = 'D:\SQLData\School_Data.mdf',
    SIZE = 50MB, MAXSIZE = 500MB, FILEGROWTH = 50MB
),
FILEGROUP [FG_Index] (                            -- 索引文件組：獨立磁碟
    NAME = 'School_Index',
    FILENAME = 'D:\SQLIndex\School_Index.ndf',
    SIZE = 100MB, MAXSIZE = 1000MB, FILEGROWTH = 100MB
),
FILEGROUP [FG_Data] (                             -- 業務數據文件組
    NAME = 'School_Data_01',
    FILENAME = 'D:\SQLData\School_Data_01.ndf',
    SIZE = 200MB, MAXSIZE = UNLIMITED, FILEGROWTH = 200MB
)
LOG ON (                                          -- 日志文件
    NAME = 'School_Log',
    FILENAME = 'D:\SQLLog\School_Log.ldf',
    SIZE = 50MB, MAXSIZE = 500MB, FILEGROWTH = 50MB
);
GO

-- 設置預設文件組為業務數據組（新建表默認落這裡）
ALTER DATABASE School MODIFY FILEGROUP [FG_Data] DEFAULT;
GO

二、 規範建表：主鍵內聯、非空/默認/檢查內聯、外鍵分離

USE School;
GO

-- 1. 年級表（父表先建）
IF OBJECT_ID('dbo.Grade', 'U') IS NOT NULL DROP TABLE dbo.Grade;
CREATE TABLE dbo.Grade (
    GradeId     INT            NOT NULL PRIMARY KEY,          -- 主鍵內聯命名：PK_Grade
    GradeName   NVARCHAR(50)   NOT NULL
) ON [FG_Data];
GO

-- 2. 學生表
IF OBJECT_ID('dbo.Student', 'U') IS NOT NULL DROP TABLE dbo.Student;
CREATE TABLE dbo.Student (
    StudentNo       INT            IDENTITY(1,1) NOT NULL,
    LoginPwd        NVARCHAR(20)   NOT NULL,
    StudentName     NVARCHAR(20)   NOT NULL,
    Sex             TINYINT        NOT NULL CHECK (Sex IN (0,1)),  -- 檢查約束內聯
    GradeId         INT            NOT NULL,
    Phone           NVARCHAR(20)   NOT NULL,
    BornDate        DATE           NOT NULL CHECK (BornDate > '1990-01-01'),
    Address         NVARCHAR(255)  NULL     DEFAULT (N'地址不詳'),  -- 默認約束內聯
    Email           NVARCHAR(50)   NULL,
    IDCard          CHAR(18)       NULL,
    CONSTRAINT PK_Student PRIMARY KEY (StudentNo),                -- 顯式命名主鍵
    CONSTRAINT UQ_Student_IDCard UNIQUE (IDCard)                  -- 顯式命名唯一約束
) ON [FG_Data];
GO

-- 3. 課程表
IF OBJECT_ID('dbo.Course', 'U') IS NOT NULL DROP TABLE dbo.Course;
CREATE TABLE dbo.Course (
    CourseNo     INT            IDENTITY(1,1) NOT NULL,
    CourseName   NVARCHAR(50)   NOT NULL,
    ClassHour    SMALLINT       NOT NULL CHECK (ClassHour > 0),
    GradeId      INT            NOT NULL,
    CONSTRAINT PK_Course PRIMARY KEY (CourseNo)
) ON [FG_Data];
GO

-- 4. 成績表（復合主鍵）
IF OBJECT_ID('dbo.Score', 'U') IS NOT NULL DROP TABLE dbo.Score;
CREATE TABLE dbo.Score (
    StudentNo    INT        NOT NULL,
    CourseNo     INT        NOT NULL,
    ExamDate     DATETIME   NOT NULL DEFAULT (GETDATE()),
    Score        TINYINT    NOT NULL CHECK (Score BETWEEN 0 AND 100),
    CONSTRAINT PK_Score PRIMARY KEY (StudentNo, CourseNo, ExamDate)
) ON [FG_Data];
GO

三、 外鍵統一後加：控制順序、命名規範、非阻塞檢查

-- 命名規範：FK_子表_父表
ALTER TABLE dbo.Student  WITH NOCHECK
ADD CONSTRAINT FK_Student_Grade FOREIGN KEY (GradeId) REFERENCES dbo.Grade(GradeId);
GO
ALTER TABLE dbo.Course   WITH NOCHECK
ADD CONSTRAINT FK_Course_Grade FOREIGN KEY (GradeId) REFERENCES dbo.Grade(GradeId);
GO
ALTER TABLE dbo.Score    WITH NOCHECK
ADD CONSTRAINT FK_Score_Student FOREIGN KEY (StudentNo) REFERENCES dbo.Student(StudentNo),
    CONSTRAINT FK_Score_Course  FOREIGN KEY (CourseNo)  REFERENCES dbo.Course(CourseNo);
GO

-- 數據導入後統一驗證並啟用約束
ALTER TABLE dbo.Student CHECK CONSTRAINT FK_Student_Grade;
ALTER TABLE dbo.Course  CHECK CONSTRAINT FK_Course_Grade;
ALTER TABLE dbo.Score   CHECK CONSTRAINT FK_Score_Student, FK_Score_Course;
GO

四、 常用約束管理片段

-- 刪除約束（需知約束名）
ALTER TABLE dbo.Student DROP CONSTRAINT DF_Student_Address;  -- 刪除默認
ALTER TABLE dbo.Student DROP CONSTRAINT CK_Student_BornDate; -- 刪除檢查

-- 修改列類型（需先刪除依賴該列的約束/索引）
ALTER TABLE dbo.Score ALTER COLUMN Score SMALLINT;  -- 擴大範圍

-- 批量刪除多個約束
ALTER TABLE dbo.Score DROP CONSTRAINT 
    PK_Score, FK_Score_Student, FK_Score_Course, CK_Score_Score, DF_Score_ExamDate;
GO

五、 命名規範速查表

六、 部署檢查清單

• [ ] 腳本可重複執行無報錯（冪等）。
• [ ] 文件組映射到不同物理磁碟（數據/索引/日志分離）。
• [ ] 所有約束顯式命名，無系統生成隨機名。
• [ ] 外鍵建立順序：父表先建、數據導入後啟用檢查。
• [ ] 大表預留足夠 FILEGROWTH，避免頻繁自動增長卡頓。
• [ ] 敏感列（身份證、手機）已加密或脫敏策略就緒。

將此模板納入項目代碼倉庫，配合 Flyway/Liquibase 或 CI/CD 流水線，實現資料庫結構的版本化、可追溯、可回滾管理。