資料庫引擎允許您創建不存在的相關記錄

我有一張桌子叫Active

在這張表中，我存盤了一家公司的資產資訊，例如

電視品牌、型號、序列號、所在位置、所在公司、部門等...

一項資產可以有 N 個類別。

一個類別可以有 n 個子類別。

例子

一個asset可以有幾個asset types

1. 地面
2. 辦公用品
3. IT設備4等

在ground子類別中只有 2 種型別 1.Rural 2. Urban

如果我在子類別為 3 時嘗試創建型別資產1. ground ，則資料庫引擎允許我創建記錄

但不應該是這樣的，因為當 ground= 1 時沒有大于 3 的子類

它們只存在 1.Rural 2. Urban

詢問

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)

uj5u.com熱心網友回復：

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

標籤：sql sql服务器

上一篇：如何使用SQL腳本重新編號SQL中的欄位值

下一篇：在unpivot中洗掉NULLS