The database engine is allowing you to create a non-existing related record-CodePudding

I have a table called Active

In this table I store the information of the assets of a company, example

TV brand, model, serial number, where it is located, in which company it is located, department etc...

An asset can have N number of categories.

A category can have n number of subcategories.

Example

an asset can have several asset types

ground
Office equipment
IT equipment 4 etc.

in the ground subcategory there are only 2 types 1.Rural 2. Urban

If I try to create an asset of type 1. ground when its subcategory is 3, the database engine allows me to create the record

But it shouldn't be like that, because when ground = 1 There is no subcategory greater than 3

they only exist 1.Rural 2. Urban

Query

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


-- 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)

CodePudding user response：

You can have a more simple model:

Example:

CREATE Table Category 
(
ID INT IDENTITY PRIMARY KEY, 
Name VARCHAR(100)
)

CREATE Table SubCategory 
(
ID INT IDENTITY PRIMARY KEY,  
Name VARCHAR(100),
IdCategory INT FOREIGN KEY REFERENCES Category(ID)
)

CREATE TABLE Asset
(
ID INT IDENTITY PRIMARY KEY, 
Name VARCHAR(100),
IdSubCategory INT FOREIGN KEY REFERENCES SubCategory(ID)
)

--JUST TO BE ABLE TO INSERT THE ID'S SO THAT THEY WILL ALWAYS BE THE SAME, IGNORE THIS PARTS
SET IDENTITY_INSERT dbo.Category ON

--Categories
INSERT INTO Category (ID, NAME) VALUES (1, 'Ground'), 
                                       (2, 'Vehicle'), 
                                       (3, 'IT Equipment')

SET IDENTITY_INSERT dbo.Category OFF

SET IDENTITY_INSERT SubCategory ON

--Ground Subcategories
INSERT INTO SubCategory (ID, NAME, IdCategory) VALUES (1, 'Rural', 1), 
                                          (2, 'Urban', 1) 

--Vehicle Subcategories
INSERT INTO SubCategory (ID, NAME, IdCategory) VALUES (3, 'Car', 2), 
                                                      (4, 'Bus', 2), 
                                                      (5, 'Aircraft', 2)

--IT Equipment Subcategories
INSERT INTO SubCategory (ID, NAME, IdCategory) VALUES (6, 'Notebok', 3), 
                                                      (7, 'Desktop Computer', 3), 
                                                      (8, 'Printer', 3)
        
SET IDENTITY_INSERT SubCategory OFF

--Rural Assets:
Insert Into Asset (NAME, idSubCategory) VALUES ('Company Ostrich Farm', 1),
                                               ('Texas Cow Farm', 1)

--Urban Assets:
Insert Into Asset (NAME, idSubCategory) VALUES ('Company Building at Sixth Street', 2),
                                               ('Office at Avenue 456', 2)

--Car Assets:
Insert Into Asset (NAME, idSubCategory) VALUES ('Mercedez Benz 437 Viper', 3),
                                               ('Fusca year 1980', 3)

--Printer Assets:

Insert Into Asset (NAME, idSubCategory) VALUES ('HP deskjet 1456', 8),
                                               ('Gainsha Tag Printer 223', 8)


--##########################
--######## QUERY ###########
--##########################

SELECT Asset.ID AS AssetId,
       Asset.Name AS AssetName,
       SubCategory.ID AS SubCategoryID,
       SubCategory.Name AS SubCategoryName,
       Category.ID AS CategoryID,
       Category.Name AS CategoryName
FROM Asset
JOIN SubCategory ON SubCategory.ID = Asset.IdSubCategory
JOIN Category ON Category.ID = SubCategory.IdCategory