Lets assume we have 2 database tables (entites). The first is named A and the second one is named B. There are 1:N (where N > 0) relationship (so every record in table A is in relation with 1+ records in B table).

Table A (for simplicity assume there is just id in this table, other table columns are not relevant for this question)

Table B

  • id (bigint) <- primary key
  • defaultForA (boolean, default = false)
  • a_id (bigint) <- foreign key for table A record

Is it possible to set table contraint that will check there is only one truthy value in defaultForA column for every present a_id value? For example we have 2 records in table A (with IDS 0,1)

And 4 records in table B

id, defaultForA,a_id





This is completely fine state. For each A record I have one "default" value from B table The constraint should not allow me to add following record to table B


Because there is alredy record linked to record 0 in table A that have defaultForA property set to true

  • You can use 2 EXCLUDE constraints for this. Exemple: create table t ( id int primary key, defaultForA bool, a_id int ); alter table t add constraint exclude_both_true exclude using btree (a_id with =) where (defaultForA); alter table t add constraint exclude_both_false exclude using btree (a_id with =) where (not defaultForA); NB: this solution is different from the link to SQL Server discussion. This solution does not depend on foreign keys but is using a Postgresql only type of consraint: the EXCLUDE constraint.
    – pifor
    Mar 19, 2020 at 16:05
  • It doesn't need EXCLUDE constraint though. It can be done with a partial/filtered UNIQUE index. The linked answer has that option, too: CREATE UNIQUE INDEX is_FavoriteChild ON Child (ParentID) WHERE IsFavorite = 1 ; Mar 19, 2020 at 17:29
  • You just have to change the table/column names and adjust for the different type (boolean vs int): CREATE UNIQUE INDEX is_default_for_a ON b (a_id) WHERE defaultForA ; Mar 19, 2020 at 17:31


Browse other questions tagged or ask your own question.