0

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

enter image description here

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.

  • A partial/filtered UNIQUE constraint should do what you want (if Alive was in table Fish): UNIQUE (FishEspecieId, Id) WHERE (Alive = 1). What is the datatype of column Alive? – ypercubeᵀᴹ Sep 11 at 13:42
  • Is Fish.ID a Foreign Key to Animal.ID? – Kirk Saunders Sep 11 at 14:41
  • @KirkSaunders Yes, both PK and FK to Animal.Id. – Mateus Felipe Sep 11 at 17:20
  • @ypercubeᵀᴹ Bit (the SQL Server boolean-like type) – Mateus Felipe Sep 11 at 17:33
  • 1
    @KirkSaunders No exactly... this is not the problem here. Basically I want to ensure something like UNIQUE(Fish.FishEspecieId) WHERE (Animal.Alive = 1), when inserting a Animal. Of course I can't use UNIQUE with multiple tables, but this is the idea. Example: I have a Fish with FishEspecieId of 1. I may only insert an Animal that will in the future have a Fish associetade with it and has FishEspecieId of 1 if this first Fish is related with an Animal that is not Alive. If it's Alive, the UNIQUE constraint fails. – Mateus Felipe Sep 11 at 18:08
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.

4

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).
  • Actually, I want to constrain Animal insert, and not Fish insert. – Mateus Felipe Sep 11 at 19:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.