January 21, 2009 at 1:49 am
I'm having Users, Tasks, and Categories:
each user has many tasks
each task may be in any or one category
each user has many categories
I 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 category
and when a task is deleted its category should not be deleted
could you give me the design of tables and it primary and foreign keys as well as cascade properties?
Thanks in advance
January 21, 2009 at 2:14 am
What problem are you facing while designing this structure?
Regards,
Nitin
January 21, 2009 at 2:33 am
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)) -> I
Category(id, UserID(FK)), when I'm going to define set null (set categoryID in Task table as null) on delete category I'm given an error stating that it produces cyclic reference
January 21, 2009 at 2:54 am
before suggesting any design have you done any worked out on any design?
Abhijit - http://abhijitmore.wordpress.com
January 21, 2009 at 3:17 am
sorry I didn't get you Abhijit
this is one of the designs that didn't work:
User(id, name), I define cascade on delete user and category
Task(id, UserID(FK), CategoryID(FK)) -> I
Category(id, UserID(FK))
Which I've mentioned in my previous post
January 21, 2009 at 4:13 am
it will really raise an error. simply because it cannot find the cascade record referenced by the parent table.
January 22, 2009 at 7:46 am
There isn't quite enough information here on which to base a complete answer. Are categories unique to specific users, or shared among all users? If it's the latter, you're probably going to need to set things up along the lines of the following:
CREATE TABLE USERS (
USERID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
USERFLD1 ....
USERFLD2 ....
.
.
.
)
CREATE TABLE TASKS(
TASKID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
TASK_NAME varchar(50),
OTHER_TASK_FIELD1 ....
OTHER_TASK_FIELD2 ....
.
.
.
)
CREATE TABLE CATEGORIES(
CATEGORYID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
CATEGORY_NAME varchar(50),
OTHER_CATEGORY_FIELD1 ....
OTHER_CATEGORY_FIELD2 ....
.
.
.
)
CREATE TABLE USER_TASKS (
USERID INT NOT NULL REFERENCES USERS.USERID,
TASKID INT NOT NULL REFERENCES TASKS.TASKID,
PRIMARY KEY CLUSTERED (USERID, TASKID)
)
CREATE TABLE USER_CATEGORIES(
USERID INT NOT NULL REFERENCES USERS.USERID,
CATEGORYID INT NOT NULL REFERENCES CATEGORIES.CATEGORYID,
PRIMARY KEY CLUSTERED (USERID, CATEGORYID)
)
CREATE TABLE TASK_CATEGORIES(
TASKID INT NOT NULL REFERENCES TASKS.TASKID,
CATEGORYID INT NOT NULL REFERENCES CATEGORIES.CATEGORYID,
PRIMARY KEY CLUSTERED (TASKID, CATEGORYID)
)
I can't recall the T-SQL details on how to then set up your cascade deletes, but this kind of structure gives you many to many through reference tables. I think you can probably figure it from here.
Steve
(aka smunson)
:):):)
peace2007 (1/21/2009)
I'm having Users, Tasks, and Categories:each user has many tasks
each task may be in any or one category
each user has many categories
I 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 category
and when a task is deleted its category should not be deleted
could you give me the design of tables and it primary and foreign keys as well as cascade properties?
Thanks in advance
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
January 23, 2009 at 10:50 pm
Thank you Steve 🙂
each user has his own categories, it may be the same as others or not
My main question is around cascade on delete which produces cyclic cascade I think I have to handle one of them via trigger
January 24, 2009 at 2:00 pm
I'm not sure you'd have to worry about using a trigger with this structure. Since you never create a record that doesn't reference something, when you delete, it can cascade successfully by deleting any TASK_CATEGORIES records whenever a task is deleted, and also when any category is deleted, because determining whether a task has a category no longer relies on looking in the task table. Same goes for users and tasks. Delete a task, and the USER_TASKS table gets a delete for the record relating that task to that user. I'm pretty sure you won't need a trigger.
I may not recall the exact T-SQL to set up a cascade delete, but with the structure I provided, you should be able to have foreign keys with cascade deletes on them without a problem, as long as all the foreign key references are pointing to the reference tables (USER_TASKS, USER_CATEGORIES, TASK_CATEGORIES), you're good to go.
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
January 25, 2009 at 1:40 am
Thank you Steve 🙂
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply