如何管理存储在多个数据库表中的值的唯一性

Question

我有单独的资产表，用于存储不同类型的物理和逻辑资产，例如：

1. 车辆表( ID，型号，EngineSize，Drivername，lastMaintenanceDate)
2. 服务器表( ID、IP、OSName等…)
3. VM (ID、大小等…)。
4. VM_IP (VM_ID，IP)

现在我遇到的问题是：-

1. 对于服务器表和VM_IP表中的IP列，我需要该列在这两个表中是唯一的，因此，例如，数据库不应该允许服务器和VM具有相同的IP。在当前的设计中，我只能单独保证表的唯一性。任何人也可以建议我如何在数据库级别上处理这个独特的需求。

问候

：：编辑：：

我现时的资料库结构如下：

​

​

目前，我看到以下几点：

1. 我在基资产表中引入了一个冗余的AssetTypeID列，这样我就可以知道资产类型，而不必加入表。这可能会破坏正常化。
2. 在我的上面的架构中，我无法控制(在数据库级别上)哪些资产应该有IP，哪些资产不应该有IP，哪些资产可以/不能有多个IP。那么有什么方法可以改进我的架构来处理这两点呢？

提前感谢您的帮助。

Answer 1

创建IP表并使用外键

Answer 2

您可以使用indexed view。

CREATE VIEW YourViewName with SCHEMABINDING
as
    ...
GO

CREATE UNIQUE CLUSTERED INDEX IX_YourIndexName
    on YourViewName  (..., ...)

Answer 3

根据您的编辑，您可以在资产表上引入一个超级键，并使用各种约束来强制执行您想要的大部分内容：

create table Asset (
    AssetID int not null primary key,
    AssetTypeID int not null
    --Skip all of the rest, foreign keys, etc, irrelevant to example
    ,constraint UQ_Asset_TypeCheck
        UNIQUE (AssetID,AssetTypeID) --This is the superkey
)

以上所述意味着现在可以在其他表中检查/强制使用AssetTypeID列，并且不存在不一致的风险。

create table Servers (
    AssetID int not null primary key,
    AssetTypeID as 1 persisted,
    constraint FK_Servers_Assets FOREIGN KEY (AssetID)
        references Asset (AssetID), --Strictly, this will become redundant
    constraint FK_Servers_Assets_TypeCheck FOREIGN KEY (AssetID,AssetTypeID)
        references Asset (AssetID,AssetTypeID)
)

因此，在上面，我们强制要求这个表中的所有条目实际上必须是正确的资产类型，方法是使它成为一个固定的计算列，然后在外键中使用它返回到超级键。

--So on for other asset types
create table Asset_IP (
    AssetID int not null,
    IPAddress int not null primary key, --Wrong type, for IPv6
    AssetTypeID int not null,
    constraint FK_Asset_IP_Assets FOREIGN KEY (AssetID)
        references Asset (AssetID), --Again, redundant
    constraint CK_Asset_Types CHECK (
        AssetTypeID in (1/*,  Other types allowed IPs */)),
    constraint FK_Asset_IP_Assets_TypeCheck FOREIGN KEY (AssetID,AssetTypeID)
        references Asset (AssetID,AssetTypeID)
)

现在，我们再次引用超级键，以确保我们已经获得了一个本地(到此表)正确的AssetTypeID值，然后我们可以在检查约束中使用该值来限制实际允许在该表中输入哪些资产类型。

create unique index UQ_Asset_SingleIPs on Asset_IP (AssetID)
   where AssetTypeID in (1/* Type IDs that are only allowed 1 IP address */)

最后，对于某些AssetTypeID值，我们确保该表仅包含该AssetID的一行。

我希望这给您足够的想法，如何实现您的各种检查基于类型。如果您想要/需要，现在可以构造一些视图(其余代码将通过这些视图进行交互)，这些视图隐藏额外的列，并提供触发器以简化INSERT语句。

另外，我建议您选择一个约定，并在涉及到表名时坚持它。我最喜欢的一个是使用复数/集体名称，除非表只打算包含一行。因此，我会将Asset重命名为Assets，或者将Asset_IP重命名为Asset_IPs。现在，你有一种混合物。