Results 1 to 14 of 14
  1. #1
    Join Date
    Mar 2009
    Posts
    27

    Unanswered: prevent row from being deleted

    hi. i have 2 forms bound to 2 tables.

    t1
    t2

    t2 has FK to t1. now if i try to delete a row from t1, i want sql server to throw an exception that the row is referenced by FK or something like this?

    in access it is 'enforce constraints' if i am not mistaken. please help.
    thank you!

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    If you have set up your foreign keys (and not added any cascade options) this will happen automatically.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Mar 2009
    Posts
    27
    where cascade option is usually specified? i have my database imported from access db where cascade option was enabled. how should i check this in sql server?

  4. #4
    Join Date
    Mar 2009
    Posts
    27
    i just want sql server warn me before deleting if any of the rows is referenced by FK

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    It cannot warn. It will either delete the related rows, or prevent you deleting them. In the latter case, you would need to handle the warning and deleting the children yourself.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    You want just a warning and then delete, or you want to entirely restrict the delete?
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  7. #7
    Join Date
    Mar 2009
    Posts
    27
    i just want warning that a row is referenced by FK from another table and that the user must delete referencing rows first.

    i can make the application warn the user, but wonder if sql server can make it.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You can pass the raw SQL Server exception text to the user, but you would be better off intercepting the message and presenting your own user friendly message.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Mar 2009
    Posts
    27
    and how to make sql server throw this exception?

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    It will throw it automatically. It would require code to prevent it throwing the exception.

    I think this could go on for a long time. Why not create two tables, create a reference, stick some data in and then try to delete the data. Everything we are discussing can just be tried out.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Mar 2009
    Posts
    27
    the problem is when i create new tables to test, it works fine, sql server does not even allow me to delete - it's fine for me. but in my imported table it just deletes the row wihtout any warning. trying to find out why. i have attached relationship properties: "Enforce key constraints" is enabled. Also delete rule is set to 'no action'
    Attached Thumbnails Attached Thumbnails enforce.JPG  
    Last edited by emilh; 07-16-09 at 08:43.

  12. #12
    Join Date
    Mar 2009
    Posts
    27
    solved. when importing db from access didn't change delete rule for some relationships. thank you for help, pootle flump

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I never work using the GUI. Could you post the DDL (right click the table and select Script Table As... -> Create) please?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Mar 2009
    Posts
    27
    it works fine now. the code is attached
    Attached Files Attached Files

Posting Permissions

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