Cascade delete 1

[Jaheel22]

When i parse query

alter table Z49_tbl (reportid int primary key)
alter table Z49Section1_tbl (reportid int foreign key references Z49_tbl (reportid) on delete cascade)

then i get following error

Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near '('.
Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near '('.

Please note that
Z49_tbl is the parent table while Z49Section1_tbl is child table. All i want that if parent recond in Z49_tbl is to be deleted, then its child records in Z49Section1_tbl should also be deleted.

I spent a lot of time to resolve it but could not.

Thanks very much for taking time.

 

[john76]

Try this:

alter table Z49_tbl alter column reportid int primary key
alter table Z49Section1_tbl alter column reportid int foreign key references Z49_tbl (reportid) on delete cascade

HTH,
John

 

[Jaheel22]

Then i get this error

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'primary'.
Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'foreign'.

Thanks again

 

[john76]

ok, actually tried it this time with some mock-ups:

alter table Z49_tbl alter column reportid int not null
Go
alter table Z49_tbl add constraint pk_Z49_tbl_reportid primary key (reportid)
alter table Z49Section1_tbl add constraint fk_Z49Section1_tbl_reportid foreign key (reportid) references Z49_tbl (reportid) on delete cascade
Go

This of course assumes that reportid of table Z49_tbl truely does not have any null rows.

HTH,
John

 

[Jaheel22]

John,

Thanks very much. It's working fine now.

Mack

 

[john76]

Follow up:
First of all, if reportid of Z49_tbl is not unique and non-null then you cannot use it as a primary key. If it qualifies these two, then you must declare it Not Null in the definition.

Second, if you are unable to use it as a primary key, then you will probably have to write a trigger on Z49_tbl to perform the functionality you describe.

Maybe something like: (note this is untested)

Create Trigger triggerCascadeDeletes
On Z49_tbl
After Delete
As
	Delete From	Z49Section1_tbl
	Where	reportid = deleted.reportid
Go

would work.

HTH,
John

 

[john76]

ok, great. You beat me to the post ;-)

John