如何设计数据库约束，以便两个实体只有两个实体之间的两个字段值匹配？

I have a database with four tables as follows:

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

Addressbook
--------------------
id
more fields

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

Contact
---------------------
id
addressbook id
more fields

联系--------------------- id addressbook id更多字段

Group
---------------------
id
addressbook id
more fields

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

Group to Contact
---------------------
Composite key
Group id
Contact id

要联系的组---------------------复合密钥组ID联系人ID

My relationships are one to many for addressbook > contact, one to many for addressbook > group and many to many between contact and groups.

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

So in summary, I have an addressbook. Contacts and groups can be stored within it and they cannot be stored in more than one addressbook. Furthermore as many contacts that are needed can be added to as many groups as are needed.

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

My question now poses as follows. I wish to add the constraint that a contact can only be a member of a group if both of them have the same addressbook id.

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

As I am not a database person this is boggling my brain. Does this mean I have designed my table structure wrong? Or does this mean that I have to add a check somewhere before inserting into the group to contact table? This seems wrong to me because I would want it to be impossible for SQL queries to link contacts to groups if they do not have the same id.

因为我不是数据库人，所以这让我难以置信。这是否意味着我设计的表结构错了？或者这是否意味着我必须在插入到联系表的组之前在某处添加支票？这对我来说似乎不对，因为我希望SQL查询无法将联系人链接到组，如果他们没有相同的ID。

5 个解决方案

#1

You should be able to accomplish this by adding a addressbook_id column to your Group to Contact bridge table, then using a compound foreign key to both the Contacts and Groups tables.

您应该能够通过向group to Contact桥接表添加addressbook_id列，然后对Contacts和Groups表使用复合外键来完成此操作。

In PostgreSQL (but easily adaptable to any DB, or at least any DB that supports compound FKs):

在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)
)

By using the same addressbook_id column in both constraints, you are of course enforcing that they are the same in both referenced tables.

通过在两个约束中使用相同的addressbook_id列，您当然会强制它们在两个引用的表中都是相同的。

#2

OK - the Many to Many is governed by the GroupToContact table.

好的 - 多对多由GroupToContact表控制。

So the constraints are between Group and GroupToContact and between Contact and GroupToContact (GTC)

因此约束在Group和GroupToContact之间以及Contact和GroupToContact（GTC）之间

Namely

亦即

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

And

和

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

So you will need to add AddressBookId to GroupToContact table

因此，您需要将AddressBookId添加到GroupToContact表中

One further note - you should not define any relationship between Contact and Group directly - instead you just define the OneToMany relationships each has with the GroupToContact table.

还有一点需要注意 - 您不应该直接定义Contact和Group之间的任何关系 - 而只需定义每个与GroupToContact表有关的OneToMany关系。

#3

As BonyT suggestion:

作为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)

#4

As A CHECK Constraint can't include sub-queries. You could create a trigger that checks that the group and contact have the same addressbookid and generate an error if they do not.

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

Although a database trigger defined to enforce an integrity rule does not check the data already in the table, I would recommended that you use a trigger only when the integrity rule cannot be enforced by an integrity constraint.

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

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

Note:(This is from memory so probably not syntactically correct)

注意:(这是来自内存所以可能在语法上不正确）

#5

In your E-R (Entity-Relationship) model, the entities Group and Contact are (or should be) "dependent entities", which is to say that the existence of a Group or Contact is predicated upon that of 1 or more other entities, in this case AddressBook, that contributes to the identity of the dependent entity. The primary key of a dependent entity is composite and includes foreign keys to the entity(ies) upon which it is dependent.

在您的ER（实体 - 关系）模型中，实体Group和Contact是（或应该是）“依赖实体”，也就是说，Group或Contact的存在是基于一个或多个其他实体的存在，这种情况是AddressBook，它有助于依赖实体的身份。从属实体的主键是复合的，并且包括它所依赖的实体的外键。

The primary key of both Contact and Group include the primary key of the AddressBook to which they belong. Once you do that, everything falls into place:

Contact和Group的主键包括它们所属的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 ) ,
)

Cheers.

干杯。