How to add constraint for deleting in sql

Question

I have a table for books, bookcategories and category

It looks like this

Book
    Id uniqueidentifier,
    Title varchar(255),
    Author varchar(255),
    Deleted datetime

BookCategory
    BookId
    CategoryId

Category
    Id uniqueidentifier
    Name varchar(255)
    Deleted datetime

I want to write a constraint, that would prevent from deleting a book, if category for that book exists

Meaning, if a book (lets say harry potter) has a category of fiction for example, table BookCategory would include both Id's ofcourse. If a user would want to delete a book that has a specific category, he'd be unable to do it.

Can some1 help me?

PS when i delete items, i dont actually delete them, but set property deleted to datetime.

"when i delete items, i dont actually delete them, but set property deleted to datetime." Then you'll have to use an `UPDATE` trigger; constraints won't help you. — Jeroen Mostert, Aug 11 '17 at 12:40
I'll add my 5 cents. What @JeroenMostert means is that you are not physically deleting record, so foreign key (the people are suggesting you to use) will not help in that way and the only way you can achieve that is to use TRIGGERs not with CONSTRAINTs. There you need to implement the logic that checks if there are any records that use it. — Dmitrij Kultasev, Aug 11 '17 at 12:44
i did get it that it won't work. what i meant was if he could show any examples for my case @DmitrijKultasev — aiden87, Aug 11 '17 at 12:46
By update trigger, I mean the things you get from [`CREATE TRIGGER`](https://learn.microsoft.com/sql/t-sql/statements/create-trigger-transact-sql). (Yes, I could write such a trigger; I don't want to. I fully expect other people to pick up the slack.) — Jeroen Mostert, Aug 11 '17 at 12:47
If you do choose to go down the triggers route, you'll have to write additional triggers for the `BookCategory` table as well if you wish to enforce (most people usually do) that you're also not allowed to create a new row in that table that references an already deleted one. — Damien_The_Unbeliever, Aug 11 '17 at 13:13

Gordon Linoff · Answer 1 · 2017-08-11T13:05:54.287

4

THIS ANSWERS THE ORIGINAL QUESTION BEFORE EDITS.

You are looking for a foreign key constraint:

alter table BookCategory add constraint fk_BookCategory_Book
    foreign key (BookId) references Book(Id);

By default, such a constraint will not allow you to delete a book that has a category. You can learn about cascading options to provide more precision in the behavior. The default when you don't provide a cascading action is ON DELETE NO ACTION, meaning that the row in Book will not be deleted.

edited Aug 11 '17 at 13:05

answered Aug 11 '17 at 12:38

Gordon Linoff

1,242,037
58
646
786

what do you mean by "the default is on delete no action". default of above constraint? – aiden87 Aug 11 '17 at 12:40
There's a small update on the question you might want to see. – Radu Gheorghiu Aug 11 '17 at 12:41
he wrote: "when i delete items, i dont actually delete them, but set property deleted to datetime" so will not work there. Jeroen commented with the right suggestion – Dmitrij Kultasev Aug 11 '17 at 12:43

score 2 · Accepted Answer · answered Aug 11 '17 at 13:02

Since you want to perform soft deletes, you can accomplish what you want by adding some additional helper columns and foreign keys:

create table Books (
    Id uniqueidentifier primary key,
    Title varchar(255) not null,
    Author varchar(255) not null,
    Deleted datetime null,
    _DelXRef as CASE WHEN Deleted is null then 0 else 1 END persisted,
    constraint UQ_Books_DelXRef UNIQUE (Id,_DelXRef)
)
create table Categories (
    Id uniqueidentifier primary key,
    Name varchar(255) not null,
    Deleted datetime null,
    _DelXRef as CASE WHEN Deleted is null then 0 else 1 END persisted,
    constraint UQ_Categories_DelXRef UNIQUE (Id,_DelXRef)
)
create table BookCategories (
    BookId uniqueidentifier not null,
    CategoryId uniqueidentifier not null,
    _DelXRef as 0 persisted,
    constraint FK_BookCategories_Books foreign key (BookID) references Books(Id),
    constraint FK_BookCategories_Books_DelXRef foreign key (BookID,_DelXRef) references Books(Id,_DelXRef),
    constraint FK_BookCategories_Categories foreign key (CategoryId) references Categories(Id),
    constraint FK_BookCategories_Categories_DelXRef foreign key (CategoryId,_DelXRef) references Categories(Id,_DelXRef)
)

Hopefully, you can see how the foreign keys ensure that the _DelXRef columns in the referenced tables have to remain 0 at all times, and so it's not possible to set Deleted to any non-NULL value whilst the row is being referenced from the BookCategories table.

(At this point, the "original" foreign keys, FK_BookCategories_Books and FK_BookCategories_Categories appear to be redundant. I prefer to keep them in the model to document the real FK relationships. I'm also using my own convention of prefixing objects with _ where it's not intended that they be used to the users of the database - they exist simply to allow DRI to be enforced)

Kudos for somehow making it work using constraints anyway. I'm somewhat on the fence on whether the general badness of triggers really outweighs the phantom columns, extra indexes and general non-obviousness of this solution, but it is a solution. — Jeroen Mostert, Aug 11 '17 at 13:07
@JeroenMostert - if a trigger happens to be disabled temporarily, you can end up with wrong data in the tables. I prefer a solution using constraints/indexes/computed columns, where possible since if they're currently checked, you know that what they seek to enforce *is* actually enforced in the data. — Damien_The_Unbeliever, Aug 11 '17 at 13:09
Don't forget that constraints are just as easy to disable, and forgetting to use `WITH CHECK` on re-enabling one is unfortunately very easy to do (since it's the default), so you can still end up with inconsistent data. Simple constraints are obviously superior to triggers; this case is a little more up to preference. — Jeroen Mostert, Aug 11 '17 at 13:17
@JeroenMostert - but there *isn't* a `WITH CHECK` type of option when re-enabling triggers. You can never know if you can trust that the constraints that they're trying to enforce are actually currently true, no matter what their current state is. That's what I was trying to point out. — Damien_The_Unbeliever, Aug 11 '17 at 13:21
Ah, I get you now. Well, you *can* know, but you have to write explicit queries to find out (or, at the very least, do something unintuitive like `SET A = A` to force the trigger to look at existing rows), which is obviously not as reliable as having the engine do the checking for you. — Jeroen Mostert, Aug 11 '17 at 13:26

score 0 · Answer 3 · answered Aug 11 '17 at 12:49

If I understand, what you are looking to do is restrict an update to the table to mark a record as "Deleted". A constraint on the table cannot do this, but you can accomplish this through an update trigger. Moreover, you should try to handle this logic in your app. Read this question that is similar to yours: Can an SQL constraint be used to prevent a particular value being changed when a condition holds?

Dmitrij Kultasev · Answer 4 · 2017-08-11T13:42:33.007

CREATE TABLE Book
(
    Id      UNIQUEIDENTIFIER
  , Title   VARCHAR(255)
  , Author  VARCHAR(255)
  , Deleted DATETIME
);

CREATE TABLE BookCategory
(
    BookId     UNIQUEIDENTIFIER
  , CategoryId UNIQUEIDENTIFIER
);

CREATE TABLE Category
(
    Id      UNIQUEIDENTIFIER
  , Name    VARCHAR(255)
  , Deleted DATETIME
);

INSERT INTO dbo.Book (   Id
                       , Title
                       , Author
                       , Deleted
                     )
VALUES (   'BCF8DE45-D26F-43EE-82CD-9975E35E51B1' -- Id - uniqueidentifier
         , 'Harry Potter'                         -- Title - varchar(255)
         , 'RK'                                   -- Author - varchar(255)
         , NULL                                   -- Deleted - datetime
       );

INSERT INTO dbo.Category (   Id
                           , Name
                           , Deleted
                         )
VALUES (   'EB6E823D-DFE8-448D-B648-EAE1EFE06358' -- Id - uniqueidentifier
         , 'Fantasy'                              -- Name - varchar(255)
         , NULL                                   -- Deleted - datetime
       );

INSERT INTO dbo.Category (   Id
                           , Name
                           , Deleted
                         )
VALUES (   '9B53C866-0DAA-4637-8169-5B8885A2E644' -- Id - uniqueidentifier
         , 'Category without books'               -- Name - varchar(255)
         , NULL                                   -- Deleted - datetime
       );

INSERT INTO dbo.BookCategory (   BookId
                               , CategoryId
                             )
VALUES (   'BCF8DE45-D26F-43EE-82CD-9975E35E51B1' -- BookId - int
         , 'EB6E823D-DFE8-448D-B648-EAE1EFE06358' -- CategoryId - int
       );
GO

CREATE TRIGGER trg_Category_Check_Category_Usage
ON Category
INSTEAD OF UPDATE
AS
    BEGIN
        IF UPDATE(Deleted)
            BEGIN
                IF EXISTS (   SELECT *
                                FROM INSERTED         i
                                JOIN dbo.BookCategory AS b ON b.CategoryId = i.Id
                               WHERE i.Deleted IS NOT NULL
                          )
                    THROW 60000, 'record exists', 1;
            END;

        UPDATE b
           SET b.deleted = i.deleted
             , b.Name = i.Name
          FROM Category b
          JOIN INSERTED i ON i.Id = b.Id;
    END;
GO
UPDATE dbo.Category SET Deleted = GETDATE()WHERE Name = 'Fantasy'; --error

UPDATE dbo.Category
   SET Deleted = GETDATE()
 WHERE Name = 'Category without books'; --no error

`INSTEAD OF` triggers are harder to maintain than `AFTER` triggers, since they must be rewritten with every change of the table structure. An `AFTER` would do just as well here -- an error raised in the trigger will roll back the original update. — Jeroen Mostert, Aug 11 '17 at 13:09

score -2 · Answer 5 · answered Aug 11 '17 at 12:42

create a primary key for id in the table category and associate it with the table bookcategory (categoryid ) with a foreign key.This will ensure you dont add an id without referencing the parent table category at the same time you cannot delete records from category table as well .

How to add constraint for deleting in sql

5 Answers5