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

  • 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

    🙂

  • 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.

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply