How to Cadcade delete related records from script

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to Cadcade delete related records from script

Author

Topic

MGA
Starting Member

28 Posts

Posted - 2010-05-21 : 03:10:26

How to cascade delete related records
and how to cascade update related fields
using T-sql (by script).
i have two tables

create table products
(
Product_Id int not null identity(1,1) constraint PK_Product_Id primary key,
Product_Name varchar(50) not null unique,
Price money not null
)
go

create table Units
(
Unit_Id int not null identity(1,1) constraint PK_Unit_Id primary key,
Unit varchar(50) not null unique
)
go

create table Products_STK
(
STK_Id int not null identity(1,1) constraint PK_PSTK_Id Primary key,
product_Id int not null constraint FK_Product_Id foreign key references Products(Product_Id),
Start_Quantity decimal(12,2),
st_unitid int constraint FK_Unit_Id foreign key references Units(Unit_Id)
)

i am developing an application using C# and sql server 2008 so when i delete any product by the proc

create proc deleteproduct(@product varchar(50))
as
begin
delete from products where Product_Name = @product
end

the application give me this message
"The DELETE statement conflicted with the REFERENCE constraint "FK_Product_Id". The conflict occurred in database "Academy", table "dbo.Products_STK", column 'product_Id'.
The statement has been terminated."

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-05-21 : 04:12:56

Just try this:

create table Products_STK
(
STK_Id int not null identity(1,1) constraint PK_PSTK_Id Primary key,
product_Id int not null constraint FK_Product_Id foreign key references Products(Product_Id) on delete cascade on update cascade,
Start_Quantity decimal(12,2),
st_unitid int constraint FK_Unit_Id foreign key references Units(Unit_Id) on delete cascade on update cascade
)

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources