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
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 '20 at 16:05CREATE UNIQUE INDEX is_FavoriteChild ON Child (ParentID) WHERE IsFavorite = 1 ;– ypercubeᵀᴹ Mar 19 '20 at 17:29CREATE UNIQUE INDEX is_default_for_a ON b (a_id) WHERE defaultForA ;– ypercubeᵀᴹ Mar 19 '20 at 17:31