I have a database with four tables as follows:

Addressbook

--------------------

id

more fields

Contact

---------------------

id

addressbook id

more fields

Group

---------------------

id

addressbook id

more fields

Group to Contact

---------------------

Composite key

Group id

Contact 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.

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.

解决方案

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.

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

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.