Results 1 to 12 of 12

Thread: DELETE on FK

  1. #1
    Join Date
    Dec 2005
    Posts
    266

    Unanswered: DELETE on FK

    I am having 2 tables :

    products :

    product_id (PK, int, not null)
    reports_id (FK, int, not null)
    name

    reports :

    reports_id (PK, int , not null)
    dateR




    If I : DELETE FROM reports WHERE reports_id = 100

    how can I create a contraints in MS SQL 2000 that automaticly all the products with reports_id = 100 are deleted too ?

    thank you

  2. #2
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Checked the "Cascade Delete Related Records" From Relationship
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  3. #3
    Join Date
    Dec 2005
    Posts
    266
    but how can I do it as a script (FROM outside)
    ALTER TABLE .... CONTRAINST .... ?

    thank you

  4. #4
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Check this...
    Code:
    ALTER TABLE products WITH NOCHECK 
    ADD CONSTRAINT exd_check FOREIGN KEY 
    	(
    		[reports_id]
    	) REFERENCES [reports] (
    		[reports_id]
    	) ON DELETE CASCADE
    Last edited by rudra; 09-22-06 at 05:20.
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  5. #5
    Join Date
    Dec 2005
    Posts
    266
    thank you rudra !

  6. #6
    Join Date
    Dec 2005
    Posts
    266
    Rudra I am having a probleme the fields are not declared as FK in the database ( I have not created that database) can I use a ON DELETE CASCADE anyway on 2 similar fields ?

    something like

    ALTER TABLE products WITH NOCHECK
    ADD CONSTRAINT exd_check ???????
    (
    [reports_id]
    ) REFERENCES [reports] (
    [reports_id]
    ) ON DELETE CASCADE


    thank you

  7. #7
    Join Date
    Aug 2004
    Location
    Pune,India
    Posts
    94
    why don't you create a on-delete trigger for this?
    In GOD we believe. Everything else we Test!

  8. #8
    Join Date
    Dec 2005
    Posts
    266
    i dont know how to do it and i run the commands from a .NET application

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Why are you trying to do this from a user interface? The job of writing triggers belongs to the developer, not the application.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Dec 2005
    Posts
    266
    i want to put it in any way in the database no matter the way

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    A trigger is a permanent database object. An application has no business creating such things on the fly.
    Either create the trigger or don't, but don't ask us to help you shoot yourself in the foot.
    If you insist upon handling relational integrity within the interface, then make two calls to the database. One to delete the child records, and one to delete the parent record.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Dec 2005
    Posts
    266
    thank you for the trigger, and helping me for database code

    for the rest i never worry for what i cannot change
    Last edited by anselme; 09-25-06 at 11:22.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •