Is it possible to have data deleted(automaticly) from tables related to the one im deleting from..
Example:
Table A have a one to many relation to 6 other tables.
I will delete a member from Table A, will the data conserning the member in the other 6 tables be automaticly removed?
Best wishes.
(If not, how should I write the sp to handle this task?)For SQL Server 7.0 you would have to implement TRIGGERS to perform cascade deletes. However in SQL Server 2000 there is cascade deletes, defined by 'Cascading Referential Integrity Constraints'|||Okay!
Thanks for responding - Scre@.m
Showing posts with label deleted. Show all posts
Showing posts with label deleted. Show all posts
Thursday, March 8, 2012
cascade delete can not apply to two foreign keys
I have a table that contains two foreign keys of two different tables. I want to build a relationship so that when either primary keys deleted in the two tables, the record in the table should be deleted. But, SQL Server does not allow me to save the relationship, it complains that the circling delete might exist. I do not know why, how can I solve this?
Table A:
ID
ProductID <foreign key>
CustomerID <foreign key
Table Product
ProductID <primary key
Table Customer
CustomerID <primary key
I want to cascade delete the record in Table A when either the ProductID is deleted from Product table or the CustomerID is deleted from Customer table.In my opinion, relying on cascading updates and deletes isn't a good thing to do anyway. I think it's better to handle the deletes in your code when you explicitly want them to happen. I.e., the procedures to delete from either the product or customer table should include a delete statement against Table A. I don't think cascading deletes are a good idea to replace your own logic.
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
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
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!!
AJHi
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
--
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!!
AJHi
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
--
Subscribe to:
Posts (Atom)