Mutually exclusive many-to-many relationships

Question

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.

Answer 1

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.

Answer 2

One option is add a containertype_id to the Container table. Make the column NOT NULL, and a foreign key to a ContainerType table, which would have entries for each type of item that can go in a container:

containertype_id |   type
-----------------+-----------
        1        | plant
        2        | animal
        3        | bacteria

To make sure the container type cannot be changed, create an update trigger that checks if the containertype_id was updated, and rolls back the change in that case.

Then, in insert and update triggers on your container link tables, check the containertype_id against the type of entity in that table, to make sure they match.

If whatever you put in a container has to match the type, and the type can't be changed, then everything in the container will be the same type.

NOTE: Since the trigger on the link tables is what will decide what matches, if you needed to have a type of container that could have plants and animals in it, you could create that type, assign it to the container, and check for that. So, you retain flexibility if things change some at some point (say, you get the types "magazines" and "books"...).

NOTE the second: If most of what happens to containers is the same, regardless of what's in them, then this makes sense. If you have very different things that happen (in the system, not in our physical reality) based on the contents of the container, then Evan Carroll's idea of having separate tables for the separate container types makes perfectly good sense. This solution establishes that containers have different types upon creation, but keeps them in the same table. If you have to check the type every time you take an action on a container, and if the action you take is dependent on the type, separate tables might actually be faster and easier.

Answer 3

If you only need 2 or 3 categories (plants/metazoa/bacteria) and you want to model an XOR relationship, maybe an "arc" is the solution for you. Advantage: no need for triggers. Example diagrams can be found [here][1]. In your situation, the "containers" table would have 3 columns with a CHECK constraint, allowing either a plant or animal or bacterium.

This is probably not appropriate if there will be the need to distinguish between lots of categories (eg genera, species, subspecies) in the future. However, for 2-3 groups/categories this may do the trick.

UPDATE: Inspired by the contributor's suggestions and comments, a different solution that allows many taxa (groups of related organisms, classified by biologist), and avoids "specific" table names (PostgreSQL 9.5).

DDL code:

-- containers: may have more columns eg for temperature, humidity etc
create table containers ( 
  ctr_name varchar(64) unique
);

-- taxonomy - have as many taxa as needed (not just plants/animals/bacteria)
create table taxa ( 
  t_name varchar(64) unique
);

create table organisms (
  o_id integer primary key
, o_name varchar(64)
, t_name varchar(64) references taxa(t_name)
, unique (o_id, t_name) 
);

-- table for mapping containers to organisms and (their) taxon, 
-- each container contains organisms of one and the same taxon
create table collection ( 
  ctr_name varchar(64) references containers(ctr_name)
, o_id integer 
, t_name varchar(64) 
, unique (ctr_name, o_id)
);

--  exclude : taxa that are different from those already in a container
alter table collection
add exclude using gist (ctr_name with =, t_name with <>);

--  FK : is the o_id <-> t_name (organism-taxon) mapping correct?
alter table collection
add constraint taxon_fkey
foreign key (o_id, t_name) references organisms (o_id, t_name) ;

Test data:

insert into containers values ('container_a'),('container_b'),('container_c');
insert into taxa values('t:plant'),('t:animal'),('t:bacterium');
insert into organisms values 
(1, 'p1', 't:plant'),(2, 'p2', 't:plant'),(3, 'p3', 't:plant'),
(11, 'a1', 't:animal'),(22, 'a1', 't:animal'),(33, 'a1', 't:animal'),
(111, 'b1', 't:bacterium'),(222, 'b1', 't:bacterium'),(333, 'b1', 't:bacterium');

Testing:

-- several plants can be in one and the same container (3 inserts succeed)
insert into collection values ('container_a', 1, 't:plant');
insert into collection values ('container_a', 2, 't:plant');
insert into collection values ('container_a', 3, 't:plant');
-- 3 inserts that fail:
-- organism id in a container must be UNIQUE
insert into collection values ('container_a', 1, 't:plant');
-- bacteria not allowed in container_a, populated by plants (EXCLUSION at work)
insert into collection values ('container_a', 333, 't:bacterium');
-- organism with id 333 is NOT a plant -> insert prevented by FK
insert into collection values ('container_a', 333, 't:plant');

Thanks to @RDFozz and @Evan Carroll and @ypercube for their input and patience (reading/correcting my answers).

Answer 4

First, I agree with @RDFozz on the reading of the question.. However he raises some concerns on stefans answer,

To address his concerns, just

1. Remove the PRIMARY KEY
2. Add the UNIQUE constraints to protect against duplicate entries.
3. Add EXCLUSION constraints to ensure the containers are "homogeneous"
4. Add an index on c_id to ensure decent performance.
5. Kill anyone who does this, point them to my other answer for sanity.

Here is what it looks like,

CREATE TABLE container ( 
  c_id int NOT NULL,
  p_id int,
  b_id int,
  a_id int,
  UNIQUE (c_id,p_id),
  UNIQUE (c_id,b_id),
  UNIQUE (c_id,a_id),
  EXCLUDE USING gist(c_id WITH =, (CASE WHEN p_id>0 THEN 1 ELSE 0 END) WITH <>),
  EXCLUDE USING gist(c_id WITH =, (CASE WHEN b_id>0 THEN 1 ELSE 0 END) WITH <>),
  EXCLUDE USING gist(c_id WITH =, (CASE WHEN a_id>0 THEN 1 ELSE 0 END) WITH <>),
  CHECK (
    ( p_id IS NOT NULL and b_id IS NULL and a_id IS NULL ) 
    OR ( p_id IS NULL and b_id IS NOT NULL and a_id IS NULL ) 
    OR ( p_id IS NULL and b_id IS NULL and a_id IS NOT NULL ) 
  )
);
CREATE INDEX ON container (c_id);

Now you can have one container with multiple things, but only one type of thing in a container.

# INSERT INTO container (c_id,p_id,b_id) VALUES (1,1,null);
INSERT 0 1
# INSERT INTO container (c_id,p_id,b_id) VALUES (1,null,2);
ERROR:  conflicting key value violates exclusion constraint "container_c_id_case_excl"
DETAIL:  Key (c_id, (
CASE
    WHEN p_id > 0 THEN 1
    ELSE 0
END))=(1, 0) conflicts with existing key (c_id, (
CASE
    WHEN p_id > 0 THEN 1
    ELSE 0
END))=(1, 1).

And it's all implemented on GIST indexes.

The Great Pyramid of Giza has nothing on PostgreSQL.

Answer 5

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.

That's a bad idea.

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

And now you know why. =)

I believe you're stuck on the idea of inheritance from object oriented programming (OO). OO Inheritance solves a problem with code-reuse. In SQL, redundant code is the least of our problems. Integrity is first and foremost. Performance is often second. We'll relish in pain for the first two. We don't have a "compile-time" that can eliminate the costs.

So just forgo your obsession for code reuse. Containers for plants, animals, and bacteria are fundamentally different in every where in the real world. The code-reuse component of "holds stuff" just won't do it for you. Break them apart. Not just will it get you more integrity and more performance, but in the future you'll find it easier to expand your schema: after all, in your schema you already had to break apart the items being contained (plants, animals, etc), seems at least possible that you'll have to break apart the containers. You're not going to want to redesign your whole schema then.