Postgresql – Mutually exclusive many-to-many relationships

constraintdatabase-designmany-to-manypostgresql

I have a table containers that can have a many-to-many relationships to several tables, let's say those are plants, animals and bacteria. Each container can contain an arbitrary number of plants, animals or bacteria, and each plant, animal or bacterium can be in an arbitrary number of containers.

So far this is very straightforward, but the part I'm having a problem with is that each container should only contain elements of the same type. Mixed containers that e.g. contain both plants and animals should be a constraint violation in the database.

My original schema for this was the following:

containers
----------
id
...
...


containers_plants
-----------------
container_id
plant_id


containers_animals
------------------
container_id
animal_id


containers_bacteria
-------------------
container_id
bacterium_id

But with this schema, I can't come up with how to implement the constraint that containers should be homogeneous.

Is there a way to implement this with referential integrity and ensuring on the database level that the containers are homogeneous?

I'm using Postgres 9.6 for this.

Best Answer

There is a way to implement this declaratively only without changing your current setup much, if you agree to introduce some redundancy to it. What follows can be considered a development on RDFozz's suggestion, although the idea fully formed in my mind before I read his answer (and it is different enough to warrant its own answer post anyway).

Implementation

Here is what you do, step by step:

  1. Create a containerTypes table along the lines of the one suggested in RDFozz's answer:

    CREATE TABLE containerTypes
(
  id int PRIMARY KEY,
  description varchar(30)
);

    Populate it with pre-defined IDs for each type. For the purpose of this answer, let them match RDFozz's example: 1 for plants, 2 for animals, 3 for bacteria.

  2. Add a containerType_id column to containers and make it non-nullable and a foreign key.

    ALTER TABLE containers
ADD containerType_id int NOT NULL
  REFERENCES containerTypes (id);

  3. Assuming the id column is already the primary key of containers, create a unique constraint on (id, containerType_id).

    ALTER TABLE containers
ADD CONSTRAINT UQ_containers_id_containerTypeId
  UNIQUE (id, containerType_id);

    This is where the redundancies begin. If id is declared to be the primary key, we can rest assured it is unique. If it is unique, any combination of id and another column is bound to be unique as well without additional declaration of uniqueness – so, what is the point? The point is that by formally declaring the column pair unique we let them be referable, i.e. to be the target of a foreign key constraint, which is what this part is about.

  4. Add a containerType_id column to each of the junction tables (containers_animals, containers_plants, containers_bacteria). Making it a foreign key is completely optional. What is crucial is to make sure the column has the same value for all rows, different for each table: 1 for containers_plants, 2 for containers_animals, 3 for containers_bacteria, according to the descriptions in containerTypes. In each case you can also make that value the default to simplify your insert statements:

    ALTER TABLE containers_plants
ADD containerType_id NOT NULL
  DEFAULT (1)
  CHECK (containerType_id = 1);

ALTER TABLE containers_animals
ADD containerType_id NOT NULL
  DEFAULT (2)
  CHECK (containerType_id = 2);

ALTER TABLE containers_bacteria
ADD containerType_id NOT NULL
  DEFAULT (3)
  CHECK (containerType_id = 3);

  5. In each of the junction tables, make the pair of columns (container_id, containerType_id) a foreign key constraint referencing containers.

    ALTER TABLE containers_plants
ADD CONSTRAINT FK_containersPlants_containers
  FOREIGN KEY (container_id, containerType_id)
  REFERENCES containers (id, containerType_id);

ALTER TABLE containers_animals
ADD CONSTRAINT FK_containersAnimals_containers
  FOREIGN KEY (container_id, containerType_id)
  REFERENCES containers (id, containerType_id);

ALTER TABLE containers_bacteria
ADD CONSTRAINT FK_containersBacteria_containers
  FOREIGN KEY (container_id, containerType_id)
  REFERENCES containers (id, containerType_id);

    If container_id is already defined to be a reference to containers, feel free to remove that constraint from each table as no longer necessary.

How it works

By adding the container type column and making it participate in the foreign key constraints, you prepare a mechanism preventing the container type from changing. Changing the type in the containers type would be possible only if the foreign keys were defined with the DEFERRABLE clause, which they are not supposed to be in this implementation.

Even if they were deferrable, changing the type would still be impossible because of the check constraint on the other side of the containers—junction table relationship. Each junction table allows only one specific container type. That not only prevents existing references from changing the type but also prevents addition of wrong type references. That is, if you have a container of type 2 (animals), you can only add items to it using the table where type 2 is allowed, which is containers_animals, and would be unable to add rows referencing it to, say, containers_bacteria, which accepts only type 3 containers.

Finally, your own decision to have different tables for plants, animals, and bacteria, and different junction tables for each entity type, already makes it impossible for a container to have items of more than one type.

So, all these factors combined ensure, in a purely declarative way, that all your containers will be homogeneous.

Related Question