如何设计数据库约束，以便两个实体只有在两个字段值匹配时才能具有多对多关系？

Question

我有一个包含四个表的数据库，如下所示：

地址簿
--------------------
id
更多字段

联系方式
----------------------------------
id
地址簿 ID
更多领域

群组
----------------------------------
id
地址簿 ID
更多字段

联系人组
----------------------------------
复合键
群组 ID
联系 ID

我的关系是一对多的地址簿 >联系人，地址簿一对多>组以及联系人和组之间的多对多。

总而言之，我有一个地址簿。联系人和群组可以存储在其中，并且不能存储在多个地址簿中。此外，可以将所需数量的联系人添加到所需数量的组中。

我现在的问题如下。我希望添加一个约束，即如果联系人都具有相同的地址簿 ID，则他们只能成为群组的成员。

由于我不是数据库人员，这让我感到困惑。这是否意味着我的表结构设计错误？或者这是否意味着我必须在插入组到联系人表之前在某处添加检查？这对我来说似乎是错误的，因为我希望如果联系人没有相同的 ID，SQL 查询就不可能将联系人链接到组。

Answer 1

您应该能够通过向联系人组桥接表添加addressbook_id列，然后对联系人使用复合外键来实现此目的> 和组表。

在 PostgreSQL 中（但可以轻松适应任何数据库，或者至少是支持复合 FK 的任何数据库）：

CREATE TABLE group_to_contact (
    contact_id          INT,
    group_id            INT,
    addressbook_id      INT,
    CONSTRAINT contact_fk FOREIGN KEY (contact_id,addressbook_id)
        REFERENCES contacts(id,addressbook_id),
    CONSTRAINT groups_fk FOREIGN KEY (group_id,addressbook_id)
        REFERENCES groups(id,addressbook_id)
)

通过在两个约束中使用相同的 addressbook_id 列，您当然可以强制它们在两个约束中相同参考表。

Answer 2

好的 - 多对多由 GroupToContact 表管理。

因此，约束位于 Group 和 GroupToContact 之间以及 Contact 和 GroupToContact (GTC) 之间

，即

 [Group].groupId = GTC.GroupId AND [Group].AddressBookid = GTC.AddressBookId

，因此

Contact.ContactId = GTC.ContactID AND Contact.AddressBookId = GTC.AddressBookId

您需要将 AddressBookId 添加到 GroupToContact 表中。

进一步注意 - 您不应该直接定义 Contact 和 Group 之间的任何关系 - 相反，您只需定义 OneToMany每个与 GroupToContact 表都有关系。

Answer 3

正如 BonyT 建议：

  Addressbook    
---------------  
*id*  
...more fields  
PRIMARY KEY (id)      

  Contact    
-----------  
*id*  
addressbook_id   
...more fields  
PRIMARY KEY (id)  
FOREIGN KEY (addressbook_id)  
    REFERENCES Addressbook(id)

  Group  
---------
*id*  
addressbook_id  
...more fields  
PRIMARY KEY (id)  
FOREIGN KEY (addressbook_id)
    REFERENCES Addressbook(id)

  Group to Contact    
--------------------   
*group_id*  
*contact_id*  
addressbook_id  
PRIMARY KEY (group_id, contact_id)  
FOREIGN KEY (addressbook_id, contact_id)  
    REFERENCES Contact(addressbook, id)
FOREIGN KEY (addressbook_id, group_id)  
    REFERENCES Group(addressbook, id)

Answer 4

作为 CHECK 约束不能包含子查询。
您可以创建一个触发器来检查组和联系人是否具有相同的地址簿 ID
如果不这样做，则会生成错误。

尽管定义为强制执行完整性规则的数据库触发器不会检查表中已有的数据，但我建议您仅在完整性约束无法强制执行完整性规则时才使用触发器。

CREATE TRIGGER tr_Group_to_Contact_InsertOrUpdate on Group_to_Contact
FOR INSERT, UPDATE AS
IF (SELECT Count(*) FROM inserted i 
 INNER JOIN Group g   ON i.groupid= g.groupid AND a.addressbookid=i.addressbookid
 INNER JOIN Address a ON a.addressbookid=I.addressbookid AND a.addressd=i.addressid) = 0
BEGIN
    RAISERROR('Address Book Mismatch', 16, 1)
    rollback tran
END

注意：（这是从记忆中得出的，因此语法上可能不正确）

Answer 5

在您的 ER（实体关系）模型中，实体 Group 和 Contact 是（或应该是）“依赖实体”，也就是说，存在 < em>组或联系人基于1个或多个其他实体（在本例中AddressBook），它们有助于身份 依赖实体的。依赖实体的主键是复合的，并且包括它所依赖的实体的外键。

联系人和组的主键都包含其所属地址簿的主键。一旦你这样做了，一切就水到渠成了：

create table Address
(
  id int not null ,
  ... ,

  primary key (id) ,
)

create table Contact
(
  address_book_id int not null ,
  id              int not null ,
  ... ,

  primary key ( address_book_id , id ) ,
  foreign key ( address_book_id      ) references AddressBook ( id ) ,
)

create table Group
(
  address_book_id int not null ,
  id              int not null ,
  ... ,

  primary key ( address_book_id , id ) ,
  foreign key ( address_book_id      ) references AddressBook( id ) ,
)

create table GroupContact
(
  address_book_id int not null ,
  contact_id      int not null ,
  group_id        int not null ,

  primary key ( address_book_id , contact_id , group_id ) ,
  foreign key ( address_book_id , contact_id ) references Contact ( address_book_id , id ) ,
  foreign key ( address_book_id , group_id   ) references Group   ( address_book_id , id ) ,
)

干杯。