SQL 2012

admin

USE Lj_AI;
GO




CREATE TABLE Lj_AI_Category (
    CategoryID INT PRIMARY KEY IDENTITY(1,1),
    CategoryName NVARCHAR(100) NOT NULL UNIQUE,
    ParentCategoryID INT NULL,
    CreateTime DATETIME DEFAULT GETDATE(),
    Description NVARCHAR(500)
);




CREATE TABLE Lj_KnowledgeBase (
    KnowledgeID INT PRIMARY KEY IDENTITY(1000,1),  -- 从1000开始计数
    Title NVARCHAR(200) NOT NULL,
    Content NVARCHAR(MAX) NOT NULL,
    CategoryID INT NOT NULL,
    Keywords NVARCHAR(500),
    CreateUser VARCHAR(50) DEFAULT SYSTEM_USER,    -- 自动记录创建者
    CreateTime DATETIME DEFAULT GETDATE(),
    UpdateTime DATETIME DEFAULT GETDATE(),
    ViewCount INT DEFAULT 0,
    IsPublished BIT DEFAULT 0,
    -- 外键约束
    CONSTRAINT FK_Knowledge_Category
    FOREIGN KEY (CategoryID)
    REFERENCES Lj_AI_Category(CategoryID)
    ON DELETE CASCADE
);


-- 标题搜索索引
CREATE NONCLUSTERED INDEX IDX_Knowledge_Title
ON Lj_KnowledgeBase(Title)
INCLUDE (Keywords, CreateTime);


-- 分类查询索引
CREATE INDEX IDX_Knowledge_Category
ON Lj_KnowledgeBase(CategoryID)
INCLUDE (IsPublished, ViewCount);






-- 查看所有表
SELECT
    name AS TableName,
    create_date AS CreateTime
FROM sys.tables
WHERE name IN ('Lj_AI_Category','Lj_KnowledgeBase');


-- 检查外键关系
SELECT
    fk.name AS ForeignKeyName,
    OBJECT_NAME(fk.parent_object_id) AS SourceTable,
    COL_NAME(fkc.parent_object_id, fkc.parent_column_id) AS SourceColumn,
    OBJECT_NAME(fk.referenced_object_id) AS TargetTable
FROM sys.foreign_keys fk
INNER JOIN sys.foreign_key_columns fkc
    ON fk.object_id = fkc.constraint_object_id;