1

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

0,true,0

1,false,0

2,true,1

3,false,1

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

4,true,0

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 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 ; – ypercubeᵀᴹ Mar 19 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 ; – ypercubeᵀᴹ Mar 19 at 17:31