How to delete Record from parent table With Primary key and child table Foreign Key Constraint Row?

• raosankar19

  Old Hand

  Points: 309

  More actions

  May 21, 2014 at 9:56 pm

  #308366

  There are 4 tables in my database.Users,User_Organization,User_Status_Code,User_Organization Role.Users have primary key column user_id.and remaining tables referencing this column user_id as primary key.If want to delete a row in Users table then remaining tables rows will effect,so I want to delete all the records which are referencing in other tables rows should delete.

  What is the command in sql server to do this

  

• Jack Corbett

  SSC Guru

  Points: 184394

  More actions

  May 22, 2014 at 11:39 am

  #1715801

  You can define the foreign key to automatically delete child rows when a parent is deleted. So the FK definition has to look like this:

  FOREIGN KEY REFERENCES users(user_id) ON DELETE CASCADE

  I typically don't use the CASCADE options for foreign keys because I typically use stored procedures for deletes so I manually handle the "cascade". So I delete from the child tables and then delete from the parent table in one transaction. The benefit to this is a I can include any business logic that may be required before deletes are done.

  Jack Corbett
  Consultant - Straight Path Solutions
  Check out these links on how to get faster and more accurate answers:
  Forum Etiquette: How to post data/code on a forum to get the best help
  Need an Answer? Actually, No ... You Need a Question