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