Restrict the occurrence of a value according to the respective value in another column in a PostgreSQL table I'm using a PostgreSQL table to link taxonomic names with the respective taxon concept as follows:

create type name_status as enum ('accepted','basionym','synonym');
create table temp (
  concept_id integer not null,
  name_id integer not null,
  status name_status,
  primary key (name_id),
  unique (name_id,concept_id)
);
insert into temp values
  (101,1,'accepted'),
  (101,2,'basionym'),
  (101,3,'synonym'),
  (101,4,'synonym'),
  (102,5,'accepted'),
  (102,6,'synonym');


The following conditions have to be fulfilled by this table:


The value 'accepted' have to appear once and only once per 'concept_id'.
The value 'basionym' may not appear in each 'concept_id', but if occurring, then only once per 'concept_id'.
The value 'synonym' may occur many times or none per 'concept_id'.


Is there a way to define such constraints without the necessity of inserting a new table in the database?
I think you can solve this with two UNIQUE partial indexes:

CREATE UNIQUE INDEX concept_status_accepted_upx
ON temp (concept_id)
WHERE status = 'accepted' ;


CREATE UNIQUE INDEX concept_status_basionym_upx
ON temp (concept_id)
WHERE status = 'basionym' ;


This will enforce that there is only one 'accepted' per concept and only one 'basionym' per conecpt.

Not sure if you want the extra but tt will NOT enforce that every concept has an 'accepted' taxonomy. This is much harder to do with DRI and the restriction of a single table.