SQL Server Performance

DB Design Q

Discussion in 'SQL Server 2005 General DBA Questions' started by peace2007, Jan 21, 2009.

  1. peace2007 New Member

    I'm having Users, Tasks, and Categories:each user has many taskseach task may be in any or one categoryeach user has many categoriesI want it when a user is deleted, his tasks and categories be deleted as well.and When a category is deleted the tasks that were in that category know that their in no categoryand when a task is deleted its category should not be deletedcould you give me the design of tables and it primary and foreign keys as well as cascade properties?Thanks in advance
  2. FrankKalis Moderator

    Obivously you would need three table: User, Tasks, and Categories and, if I understood correctly 2 more intersection tables UsersTasks and TasksCategories.
    Deleting a User is straightforward bottom up from TasksCategories to UserTasks to Users.
    Deleting a Task, to be honest, I haven't understood what you're asking for...
  3. peace2007 New Member

    Thanks for the reply
    When I define cascade or set null on update/delete of their FK references, a cyclic problem occurs:
    User(id, name), I define cascade on delete user and category
    Task(id, UserID(FK), CategoryID(FK))

    Category(id,UserID(FK)), when I'm going to define set null (set categoryID in Tasktable as null) on delete category I'm given an error stating that itproduces cyclic reference
  4. Adriaan New Member

    You will run into this limitation once every so often. It would have been nice if the cyclic-redundancy check would consider the actual columns involved in the cascading relationship, rather than the tables as a whole, but I guess that would create problems with the atomicity requirements.
    One work-around is to implement that last unacceptable relationship as a trigger that fires for update and/or delete.

Share This Page