Constrain unique value between multiple tables

Question

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<Fish>()

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

    Animal animal = DB.Insert<Animal>({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.

Answer 1

Talking with Mateus in the discussion he mentioned that he wants to avoid an insert onto the Animal table in this kind of instance if at all possible. As he and I discussed it is difficult since all conditions do not live in the same place.

Using a Trigger you should be able to remove the Animal table record for an instance where an Insert into Fish fails this condition. You will need conditions for both Insert and Update since we most likely won't want to delete the Animal table record via the Trigger on Updates.

CREATE TRIGGER dbo.FishAliveInsert ON dbo.Fish
AFTER INSERT
AS
BEGIN
    IF EXISTS
    (
        SELECT 1
        FROM INSERTED I
            INNER JOIN Fish F
                ON F.FishEspecieId = I.FishEspecieId
            INNER JOIN Animal A
                ON F.ID = A.ID
        WHERE A.IsAlive = 1
    )
    BEGIN
        RAISERROR ('This Fish cannot be added because a Fish of this FishEspecieId already exists and is Alive.', 16, 1);
        ROLLBACK TRANSACTION; --stops the Insert

        DELETE FROM Animal WHERE ID = (SELECT ID FROM INSERTED) --I am a little less confident in the correct syntax here.  

        RETURN 
    END
END

CREATE TRIGGER dbo.FishAliveUpdate ON dbo.Fish
AFTER UPDATE
AS
BEGIN
    IF EXISTS
    (
        SELECT 1
        FROM INSERTED I
            INNER JOIN Fish F
                ON F.FishEspecieId = I.FishEspecieId
            INNER JOIN Animal A
                ON F.ID = A.ID
        WHERE A.IsAlive = 1
    )
    BEGIN
        RAISERROR ('This Fish cannot be updated because a Fish of this FishEspecieId already exists and is Alive.', 16, 1);
        ROLLBACK TRANSACTION; --stops the Insert        
        RETURN 
    END
END

One piece of this you will still need to handle is what if an Animal record is added but nothing an a given Sub-Animal table?

I think that you could create a simple SQL Agent Job that Deletes records in the Animal table which don't exist in all the various Sub-Animal tables, and that Animal table record is of a certain age (5 minutes old, 10 minutes old, etc...). You would need to have a column that captures when the Animal record was added/last modified, but outside of that you should be good to go.

You could use the already proven answer given by David Browne and just implement this idea of a SQL Agent Job to do an Animal Table cleanup after the fact. He probably recommended it because it performs better or uses less resources than a Trigger so that may very well be the way to go.

Answer 2

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).