如何在子表中有数据的表上强制唯一性

Question

在一个绿地项目中，我有一个实体Resource，它有许多子实体Attributes。为了对模式建模，我在attributes表中创建了一个FK。

Resource有name和id，Attributes有resourceId，attribute name和attribute value。

对于资源名称，number of attributes和name and value of those attributes必须是唯一的。我无法在数据库级别强制资源的唯一性。

例如，valid (id:1) has (a:1，b:abc) has (a:1，b:abc) has (a:1) valid mmg(id:3) has(a:1，b:abc) not valid

在资源表上，mmg(id:2)是完全有效的，因为与其他属性相比，它只有一个属性，但mmg (id:3)不是，因为它与mmg (id:1)具有相同的属性。它基本上是相同的资源。

mmg(id:3)不应该存在，因为它们具有相同的name、attributes和same number of attributes。我可以通过编程来强制唯一性，但是我想知道是否有方法在数据库级别强制实施它，并可能重新设计表或查询，以便更容易和有效地查找。

​

​

我需要查询这个表，并使用它的属性找到确切的资源。为此，我编写了以下查询，并运行良好(虽然可能不会优化，因为资源可以有许多属性，并且查询将变得非常长)：

select * from (SELECT 
    groupedByResourceId.resource_id
FROM
    (SELECT 
        taggedElements.resource_id,
            COUNT(*) AS count1,
            SUM(taggedElements.a_ind) AS a_sum,
            SUM(taggedElements.b_ind) AS b_sum
    FROM
        (SELECT 
        resource_id,
            CASE
                WHEN (name = 'a' AND value = '1') THEN 1
                ELSE 0
            END AS a_ind,
            CASE
                WHEN (name = 'b' AND value = 'abc') THEN 1
                ELSE 0
            END AS b_ind
    FROM
        resource_attribute) AS taggedElements
    GROUP BY 1) AS groupedByResourceId
WHERE
    groupedByResourceId.count1 = 2
        AND groupedByResourceId.a_sum = 1
        AND groupedByResourceId.b_sum = 1) as attr, resource where resource.id = attr.resource_id

注意:平均每个资源最多可以有20个属性(包括0个)。

注意: resourceId正在被其他几个表使用。

Answer 1

我能想到的最好方法是在Attributes表上为name和value这两个列添加一个惟一的约束

ALTER TABLE Attributes ADD UNIQUE `idx` (`name`, `value`);

这将确保给定的resource_id必须具有name和value的唯一组合。

您可以编写一个触发器来检查插入后给定属性的行数是否不会超过20行。