Post reply

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

  • 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

    🙂

  • 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

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

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