cascade delete

[crystalized]

I am more familiar with access than SQL Server and I am wondering if the tables (when you create a 1:N relationship with a foriegn key in the N table) will perform a cascade delete.&nbsp;&nbsp;If I delete a record in the 1 table do I automatically delete the related records in the N table.<br><br>The reason I ask this is that I just tried to delete a record from this type of set up and it would not let me.&nbsp;&nbsp;Unfortunately I don't have the error message text to show you but it had to do with foreign keys. So I double checked my relationship thinking I might have it going the wrong direction, but it looked right to me. <br><br>I hope that I do not have to manually delete the N table records before I can delete the 1 table record.<br><br>Thanks in advance for helping out a rookie SQL Server user. <p>Crystal<br><a href=mailto:crystals@genesis.sk.ca>crystals@genesis.sk.ca</a><br><a href= > </a><br>--------------------------------------------------<br>
Experience is one thing you can't get for nothing.<br>
-Oscar Wilde<br>

 

[gummadi]

Hi,<br>There is no cascade delete option in sqlserver . we have to handle it with triggers. Even the foriegnkey relation has to maintained using trigger rather than using foriegn constraint because when i had written a trigger for cascade delete i found that forien key is getting fired first.<br>In order to avoid that we have write trigger to maintain <br>foriengn key, then write trigger for cascade delete.<br><br>&nbsp;foriegnkey relation&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;cascaderelation&nbsp;&nbsp;&nbsp;&nbsp;function<br>&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;constraint&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;trigger&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;fails<br>&nbsp;&nbsp;&nbsp;trigger&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;trigger&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;sucess<br><br><br>bye<br><br>gummadi

 

[jerm]

Hello Crystalized,<br>Unfortunately, gummadi is right but there is some good news.&nbsp;&nbsp;I just read an article that says SQL SERVER 2000 will have cascading update and delete options.

 

[gummadi]

Hi,<br>yes,you are right Jerm, we are having&nbsp;&nbsp;cascade delete option in <br>SQL SERVER 2000. <br><br>gummadi<br><br>