Relationships: Cascade delete

Hi,

I Use SQL Server 2005 Express edition. I have a few tables, which are inter-related. For e.g. I have a project table (ProjectID, Name, ...)

I also have a Project invoice table, in which ProjectID is referred as Foreign key.

Now, I want all the rows in Project invoice (child) table table to be deleted (for a particular ProjetID), if I delete the coresponding ProjectID in Projects (parent) table. If I use the 'on delete set null' or 'on delete cascade' constraint, only the ProjectID (in child table) is nullified whereas rest of the columns have data.

Is there any way out, where the whole ROW (not just that field) is deleted? Or, is it that this can be achieved only by some stored procedure / external program?

Thanks!

Hi !

That is done using "On cascade delete" option. The Null option does the effect you described first, setting the foreign key field to Null. Seems that you have an old vesion of the foreign key in place. Drop the Constraint and recreate it using the "on delete cascade" keywords and you will see that the row will be deleted.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de