CODEWITHSUNDEEP
sqlsql-serverconstraints
aiden87
aiden87

Reputation: 969

How to add constraint for deleting in sql

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.

Upvotes: 1

Views: 960

Answers (5)

Dmitrij Kultasev
Dmitrij Kultasev

Reputation: 5745

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

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269643

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.

Upvotes: 4

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239656

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)

Upvotes: 2

Elias Nunez
Elias Nunez

Reputation: 1

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?

Upvotes: 0

Aparna
Aparna

Reputation: 286

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 .

Upvotes: -2

Related Questions