You must Sign In to post a response.
  • Category: SQL Server

    Delete foreign key constraint Before Deleting Primary Key


    Are you looking for a way to use CascadeOnDelete ? want to delete a record from column ? then read this thread to know more about it



    Hi

    I have two Table tblCustomerMaster ,tblOrder
    in tblCustomerMaster CustomerID is Primary Key Which Is Foregin Key on tblOrder

    I deleting CustomerID at time Error was occoured

    How to delete CustomerID in tblCustomerMaster and
    tblOrder ?

    how to use CascadeOnDelete ?

    replay Fast its Urgent.
  • #722063
    Hi,

    As you are having child record in table, you can not be able to delete parent record table unless you deleting the child records first.

    So delete the child records then you can delete parent record.

    Otherewise remove constraints and delete the parent record.


    thanks...

    I Hope To Use Full..
    And Choice The Best Answers

    php-wordpress Developer
    you can any problem to php wordpress to contact me
    jivanibhai7306@gmail.com
    Thanks!!...

  • #722065
    Hai,

    If you want to delete primary key column in child table you must drop primary and foreign key constraints and then move to drop column

    Try this might be helpful....

    ALTER TABLE tblOrder
    DROP CONSTRAINT <-WRITE constraint name for foreign key->

    ALTER TABLE tblCustomerMaster
    DROP CONSTRAINT <-WRITE constraint name for primary key->

    ALTER TABLE tblOrder
    DROP COLUMN CustomerID

    ALTER TABLE tblCustomerMaster
    DROP COLUMN CustomerID


    Regards,
    Srujana

  • #722068
    Hi,

    First off all you need to break foreign key relation. Then only you can delete Primary key of child table.

    Use below code snippet.




    CREATE TABLE tblCustomerMaster2
    (
    CustomerID INT NOT NULL,
    CUSTNAME VARCHAR(100),
    PRIMARY KEY (CustomerID)
    )
    CREATE TABLE tblOrder2
    (
    Order_Id int NOT NULL,
    CustomerID int,
    PRIMARY KEY (Order_Id),
    FOREIGN KEY (CustomerID) REFERENCES tblCustomerMaster2(CustomerID)
    )

    SELECT * FROM tblCustomerMaster2
    SELECT * FROM tblOrder2

    ALTER TABLE tblOrder2
    DROP CONSTRAINT FK__tblOrder2__Custo__52FABD3B

    ALTER TABLE tblCustomerMaster2
    DROP CONSTRAINT PK__tblCusto__A4AE64B827201BD8

    ALTER TABLE tblCustomerMaster2
    DROP COLUMN CustomerID

    ALTER TABLE tblOrder2
    DROP COLUMN CustomerID

    SELECT * FROM tblCustomerMaster2
    SELECT * FROM tblOrder2



    hope this information is helpful to you...

    Mark this post if it helps you...

    --------------------------------------------------------------------------------
    Give respect to your work, Instead of trying to impress your boss.

    N@veen
    Blog : http://naveens-dotnet.blogspot.in/

  • #722083
    Hi,

    use this query...

    ALTER TABLE tblOrder2 DROP CONSTRAINT CONSTRAINT_NAME
    ALTER TABLE tblOrder2 ADD CONSTRAINT CONSTRAINT_NAME foreign key(tblOrder2) references tblCustomerMaster2(CustomerID) ON DELETE CASCADE


    Now u can easily delete master table records


    Regards,
    Sri

  • #722103
    Hi,

    you can delete fist tblOrder foreign key data and then delete primary key data. Use below query to try it.


    delete from tblOrder where CustomerID='101'
    delete from tblCustomerMaster where CustomerID='101'

    Regards
    N.Ravindran
    Your Hard work never fails

  • #726163
    Delete Child Records and after Parent Records otherwise remove the constraints in that table...

    Regards
    Sriram.R


  • This thread is locked for new responses. Please post your comments and questions as a separate thread.
    If required, refer to the URL of this page in your new post.