Constrain unique value between multiple tables I have a database where there's one base table that is extended (by foreign keying) by other tables. Something like this:



I've been assigned a task where I have to do the following check before inserting an "Animal":


Animals Id must be UNIQUE
(Thus) Fishes Id must be UNIQUE
There may be ZERO OR MORE "dead" (Alive = False) Fishes for a specific FishEspecieId
There may be ZERO OR ONE "alive" (Alive = True) Fish for a specific FishEspecieId




The task suggested that I do it on API insert method, something like this (pseudo-code):

Method Insert(Fish fish)
    Fish[] fishes = DB.GetAll&lt;Fish&gt;()

    if (fishes.Any(f =&gt; f.FishEspecieId == fish.FishEspecieId f.Animal.Alive))
        throw "Only one alive fish allowed"

    Animal animal = DB.Insert&lt;Animal&gt;({alive: true})
    DB.Insert({Id: animal.Id, ...})


I'm actually going to, indeed, do a check in the API. However, I think it's a good idea to also ensure the rules on the DB side.

How may I do this in SQL Server? And is this indeed a good idea or should I keep only in the backend side?



Ps. This is only a MRE. I know that if this was my database I should make it a single table. If that was the case, I would simply use UNIQUE(FishEspecieId) WHERE (Alive = 1). In my case, however, I have many tables that extends "Animal", or something like this.
You can enforce this with an Indexed View (or a trigger, of course).  EG

create table Animal(Id int primary key, Alive bit)

create table Fish(Id int primary key references Animal, FishEspecieId int)

go
create or alter view vFish_Alive
with schemabinding
as
select f.Id, f.FishEspecieId, a.Alive
from dbo.Fish f
join dbo.Animal a
  on f.Id = a.Id 
where a.Alive = 1

 go

 create unique clustered index pk_vFish_Alive on vFish_Alive(FishEspecieId)

 go
 insert into Animal(id,alive) values (1,1),(2,1)

insert into Fish(id,FishEspecieId) values (1,1)

insert into Fish(id,FishEspecieId) values (2,1)
--Cannot insert duplicate key row in object 'dbo.vFish_Alive' with unique index 'pk_vFish_Alive'. The duplicate key value is (1).