sqlite - 确保产品和标签属于同一帐户的数据库级约束？

Question

假设我有这些表:

CREATE TABLE "account" (
  "id" INTEGER PRIMARY KEY
  -- more fields
);

CREATE TABLE "product" (
  "id" INTEGER PRIMARY KEY,
  "accountId" INTEGER NOT NULL,
  -- more fields
  CONSTRAINT "fk_product_accountId" FOREIGN KEY("accountId") REFERENCES "account"("id") ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE TABLE "tag" (
  "id" INTEGER PRIMARY KEY,
  "accountId" INTEGER NOT NULL,
  -- more fields
  CONSTRAINT "fk_tag_accountId" FOREIGN KEY("accountId") REFERENCES "account"("id") ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE TABLE "product_tag" (
  "productId" INTEGER NOT NULL,
  "tagId" INTEGER NOT NULL,
  PRIMARY KEY("productId", "tagId"),
  CONSTRAINT "fk_tag_productId" FOREIGN KEY("productId") REFERENCES "product"("id") ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT "fk_product_tagId" FOREIGN KEY("tagId") REFERENCES "tag"("id") ON UPDATE CASCADE ON DELETE CASCADE
);

...换句话说，一个帐户可以拥有与该帐户相关联的产品和标签，然后可以将这些标签与这些产品相关联。

有没有办法在数据库级别定义一个约束来检查 product_tag组合是否有效，即 tag和 product两者都属于同一个帐户，这样我就可以避免在 INSERT 期间检查此有效性陈述？

我想到了一个额外的专栏 accountId在 product_tag ，但我不认为我可以在此定义多个外键，我也不认为这实际上会按照我的意图检查约束。

做 CHECK约束提供了这种复杂程度，也许？

Answer 1

这个怎么样？

Account:
   AccountID int not null

Tag:
   AccountID int not null
   TagID int not null
   primary key (AccountID, TagID)
   foreign key (AccountID) references Account(AccountID)

Product:
   AccountID int not null
   ProductID int not null
   primary key (AccountID, ProductID)
   foreign key (AccountID) references Account(AccountID)

ProductTag:
   AccountID int not null,
   TagID int not null,
   ProductID int not null,
   primary key(AccountID, TagID, ProductID)
   foreign key(AccountID, TagID) references Tag(AccountID, TagID)
   foreign key(AccountID, ProductID) references Product(AccountID, ProductID)

在 Tag 上使用复合键而不是身份键和 Product使我们能够做到这一点。我发现使用身份键(代理键)可以限制约束的有用性，因为表中包含的“信息”更加“分散”，并且约束只能在一个或两个表上起作用，松散地说。我真的很喜欢这个场景作为这种效果的一个例子。使用第一个设计将迫使我们使用触发器来执行规则。

我有兴趣看到其他人对此的解决方案......