CASCADE !

I am creating two tables category_group & category.
category references category_group.
When a category group is deleted i want to delete all associated categories
also.
Is what i have below the correct way to do this?
/* Tables to manage categories; Applies to users and events*/
CREATE TABLE category_group
(category_group_id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
category_group_name VARCHAR(30))
CREATE TABLE category
(category_id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
category_name VARCHAR(30),
category_group INT NOT NULL REFERENCES category_group(category_group_id)
ON DELETE CASCADE)
Help Appreciated!!
AJ
Hi
Defining a cascading Foreign Key (as you have) will do this for you. If you
want to know more see
the subject "Creating and Modifying FOREIGN KEY Constraints" in Books
online.
I prefer to name the keys so my DDL and example data would be like:
CREATE TABLE category_group
(category_group_id INT IDENTITY(1,1) CONSTRAINT PK_Category_Group PRIMARY
KEY CLUSTERED,
category_group_name VARCHAR(30))
CREATE TABLE category
(category_id INT IDENTITY(1,1) CONSTRAINT PK_Category PRIMARY KEY
CLUSTERED,
category_name VARCHAR(30),
category_group INT NOT NULL CONSTRAINT FK_Category_Category_Group
REFERENCES category_group(category_group_id)
ON DELETE CASCADE)
INSERT INTO Category_group (category_group_name) VAlUES ( 'Group 1' )
INSERT INTO Category_group (category_group_name) VAlUES ( 'Group 2' )
INSERT INTO Category_group (category_group_name) VAlUES ( 'Group 3' )
INSERT INTO category ( category_name, category_group)
VALUES ( 'Cat 1' , 1 )
INSERT INTO category ( category_name, category_group)
VALUES ( 'Cat 2' , 2 )
INSERT INTO category ( category_name, category_group)
VALUES ( 'Cat 3' , 3 )
INSERT INTO category ( category_name, category_group)
VALUES ( 'Cat 4' , 2 )
INSERT INTO category ( category_name, category_group)
VALUES ( 'Cat 5' , 1 )
BEGIN TRANSACTION
SELECT * FROM Category_Group
SELECT * FROM Category
DELETE FROM Category_Group WHERE category_group_id = 1
SELECT * FROM Category_Group
SELECT * FROM Category
DELETE FROM Category_Group WHERE category_group_id = 2
SELECT * FROM Category_Group
SELECT * FROM Category
ROLLBACK TRANSACTION
John
CREATE TABLE category
> (category_id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
> category_name VARCHAR(30),
> category_group INT NOT NULL REFERENCES
category_group(category_group_id)
> ON DELETE CASCADE)
"Anthony Judd" <adam.jknight@.optusnet.com.au> wrote in message
news:uYAHCV4oEHA.3876@.TK2MSFTNGP15.phx.gbl...
> I am creating two tables category_group & category.
> category references category_group.
> When a category group is deleted i want to delete all associated
categories
> also.
> Is what i have below the correct way to do this?
>
> /* Tables to manage categories; Applies to users and events*/
> CREATE TABLE category_group
> (category_group_id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
> category_group_name VARCHAR(30))
> CREATE TABLE category
> (category_id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
> category_name VARCHAR(30),
> category_group INT NOT NULL REFERENCES
category_group(category_group_id)
> ON DELETE CASCADE)
> Help Appreciated!!
> AJ
>
|||In addition to John's reply, don't forget to declare proper keys. You have
nullable columns and no natural key in either table:
CREATE TABLE category_group
(category_group_id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
category_group_name VARCHAR(30) NOT NULL UNIQUE)
CREATE TABLE category
(category_id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
category_name VARCHAR(30) NOT NULL UNIQUE,
category_group INT NOT NULL REFERENCES category_group(category_group_id)
ON DELETE CASCADE)
David Portas
SQL Server MVP