资产分类与子分类关联校验失效问题求助

业务场景与问题 #

我有一张Activo表，用于存储公司资产信息，包含电视品牌、型号、序列号、存放位置、所属公司、部门等字段。

资产与分类的层级关系：

• 一个资产可对应多个ActivoTipo（分类）
• 每个分类可对应多个ActivoTipoCategoria（子分类）

示例分类：

1. ground
2. Office equipment
3. IT equipment

其中ground分类（ID=1）仅包含2个子分类：

1. Rural
2. Urban

核心问题：当创建分类为ground（ID=1）、子分类ID为3的资产时，数据库允许该记录生成，但这不符合业务逻辑——因为ground分类下不存在ID大于2的子分类。

关联查询语句 #

select 
    a.id,
    a.IdActivoTipo,
    tipo.Nombre as 'Categoria',
    cat.Id,
    cat.Nombre as 'SubCategoria'

from Inventario.Activo as a
inner join Inventario.AcTipoLista as lista ON lista.Id = a.IdActivoTipo
inner join Inventario.ActivoTipo as tipo ON tipo.Id = a.IdActivoTipo
inner join Inventario.ActivoTipoCategoria as cat on cat.Id = a.IdActivoTipoCategor

表结构与关联约束SQL #

-- ACTIVO
CREATE TABLE [Inventario].[Activo]
(
    [Id] INT IDENTITY(1,1),
    [IdInfoCompania] INT,
    [IdDepartamento] INT,
    [IdUnidad] INT,
    [IdLocalidad] INT,
    [IdActivoTipo] INT,
    [IdActivoTipoCategoria] INT,
    [Marca] VARCHAR(100) NULL,
    [Color] VARCHAR(100) NULL,
    [Modelo] VARCHAR(100) NULL,
    [Componentes] VARCHAR(500) NULL,
    [Serial] VARCHAR(100) NULL,
    [Condicion] BIT DEFAULT(1) NULL,
    [Observaciones] VARCHAR(500),
    [FechaCreacion] DATETIME DEFAULT (GETDATE()),
    CONSTRAINT PK_Activo_ID PRIMARY KEY (Id)
)

-- TABLE ActivoTipo
CREATE TABLE [Inventario].[ActivoTipo]
(
    [Id] INT IDENTITY(1,1),
    [Nombre] VARCHAR(101) NOT NULL,
    [FechaCreacion] DATETIME DEFAULT (GETDATE()),
    CONSTRAINT UNIQ_NombreActivoTipo UNIQUE(Nombre),
    CONSTRAINT PK_ActivoTipo_ID PRIMARY KEY(Id),
)
GO

CREATE TABLE [Inventario].[ActivoTipoCategoria]
(
    [Id] INT IDENTITY(1,1),
    [Nombre] VARCHAR(500),
    [IdActivoTipo] INT,
    CONSTRAINT UNIQ_NombreActivoTipoCategoria UNIQUE(Nombre),
    CONSTRAINT PK_ActivoTipoCategoria_ID PRIMARY KEY(Id)
)

CREATE TABLE [Inventario].[AcTipoLista]
(
    [Id] INT IDENTITY(1,1),
    [IdActivo] INT,
    [IdActivoTipo] INT,
    [IdActivoTipoCategoria] INT,
    CONSTRAINT PK_AcTipoLista_ID PRIMARY KEY(Id)
)

--RELACIONES
-- InfoCompania_Activo
ALTER TABLE  [Inventario].[Historial]
ADD
    CONSTRAINT FK_HistorialInfoCompania_ID FOREIGN KEY (IdInfoCompania) REFERENCES [Compania].[InfoCompania](Id),
    CONSTRAINT FK_HistorialActivo_ID FOREIGN KEY (IdActivo) REFERENCES [Inventario].[Activo](Id)
GO
-- Activo
ALTER TABLE  [Inventario].[Activo]
ADD 
    CONSTRAINT FK_InfoCompania_ID FOREIGN KEY (IdInfoCompania) REFERENCES [Compania].[InfoCompania](Id),
    CONSTRAINT FK_Departamento_ID FOREIGN KEY (IdDepartamento) REFERENCES [Compania].[Departamento](Id),
    CONSTRAINT FK_Unidad_ID FOREIGN KEY (IdUnidad) REFERENCES [Compania].[Unidad](Id),
    CONSTRAINT FK_Localidad_ID FOREIGN KEY (IdLocalidad) REFERENCES [Compania].[Localidad](Id)
GO

-- AcTipoLista
ALTER TABLE [Inventario].[AcTipoLista]
    ADD CONSTRAINT FK_AcTipoLista_Activo_ID FOREIGN KEY(IdActivo) REFERENCES [Inventario].[Activo](Id),
        CONSTRAINT FK_AcTipoLista_ActivoTipo_ID FOREIGN KEY(IdActivoTipo) REFERENCES [Inventario].[ActivoTipo](Id),
        CONSTRAINT FK_AcTipoLista_ActivoCategoria_ID FOREIGN KEY(IdActivoTipoCategoria) REFERENCES [Inventario].[ActivoTipoCategoria](Id)
-- ActivoTipoCategoria
ALTER TABLE [Inventario].[ActivoTipoCategoria]
    ADD CONSTRAINT FK_ActivoTipoCategoriaID FOREIGN KEY (IdActivoTipo) REFERENCES [Inventario].[ActivoTipo](Id)

数据截图说明 #

• 资产分类数据截图
• 子分类数据截图
• 资产记录截图

内容的提问来源于stack exchange，提问作者Alexandra