ON DELETE CASCADE problem for same table column constraint

Hello,
I would like to have table with foreign key referencing to the column of the
same table. And I want to specify ON DELETE CASCADE to this column, e.g.:
create table category (
ID INTEGER IDENTITY(1,1) NOT NULL,
PARENT_ID INTEGER NULL,
NAME VARCHAR(100) NOT NULL,
CONSTRAINT CAT_PK PRIMARY KEY (ID),
CONSTRAINT CAT_FK FOREIGN KEY (PARENT_ID)
REFERENCES CAT(ID) ON DELETE CASCADE
)
But I receive error:
--
Error: java.sql.SQLException: Introducing FOREIGN KEY constraint 'CAT_FK'
on table 'category' may cause cycles or multiple cascade paths.
Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints., SQL State: S1000, Error Code: 1785

It means I cannot specify ON DELETE CASCADE clause.
HOW CAN I ENSURE CASCADE DELETING FOR ALL RECORDS (ALL CATEGORIES WITH BELONGED SUBCATEGORIES)?
IS THERE POSSIBILITY TO USE STORED PROCEDURE FOR THIS?
CAN YOU POST AN EXAMPLE PLEASE?

Thank you in advance
best regards,
Julian LegenyThis is a restriction in all versions of SQL Server that supports cascading constraints. You will have to implement the cascade action using triggers or in your SPs that perform the data modifications.

on delete cascade on the same table - is it possible?

Hi !
as u should know this code doesn't work ..
CREATE TABLE tempo (
cid int,
parent_id int,
PRIMARY KEY (cid),
FOREIGN KEY (parent_id) REFERENCES tempo ON DELETE CASCADE
)
any suggestion to achieve this ?
thx!Create a trigger to delete all the records with the same criteria. When a record is deleted in that Table.

This Code may help

create trigger delcascadetrig
on tempo
for delete
as
delete parent_id
from tempo, deleted
where tempo.cid = deleted.cid

ON DELETE CASCADE

hello guys!

Well im using MS SQL 2000 and i have over 250 Tables in one of my Database the problem is all the Foreign Key in the Table is not turn ON in ON DELETE CASCADE.

The question is, is there anyway to write a script to turn the ON DELETE CASCADE on all the Tables?

Thanks for the reply!

Novelle

Nope, not really. You will just have to script your keys out, then change to ON DELETE CASCADE manually. Unless there is some tool to do it, but I don't know of it.

A trick I would use is to take your database scripts (or if you don't have any, use a comparison tool, compare my full database to an empty one) and just replace all ON DELETE NO ACTION values with ON DELETE CASCADE. Then use your script to build a database that matched your database, except for the CASCADE constraints.

Then just do the comparison again, and let the tool do the work. (I use RedGate personally, but there are others)

sql