#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    23
    Rep Power
    0

    Deleting Related Records


    I have a primary table with FK relationships to three other tables. If I delete a record in the primary table, what happens to related records in the FK tables?
  2. #2
  3. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,925
    Rep Power
    1045
    Hi,

    depends on what action you set for ON DELETE: NO ACTION, RESTRICT, CASCADE (delete child records), SET DEFAULT or SET NULL
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2006
    Posts
    205
    Rep Power
    14
    Originally Posted by Jacques1
    Hi,

    depends on what action you set for ON DELETE: NO ACTION, RESTRICT, CASCADE (delete child records), SET DEFAULT or SET NULL

    Jacques1 is right.

    More info:

    NO ACTION = you can't delete the FK record (you'll get an error msg)

    RESTRICT = (i don't know this option, never seen in Firebird)

    SET DEFAULT = if you delete the FK record the parent table value will be set to that columns default value (if the default value doesn't exist in parent table then you'll get an error)

    SET NULL = you can delete the FK table record and the child table value will be set to NULL (this is the most permissive option, but you'll get an error if the column is set as NOT NULL)


    You have to think well which option is best for your scenario.

    If your child record is not correct without the parent record then you should choose CASCADE mode (this is what we use most of the time)
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,055
    Rep Power
    383
    If your child record is not correct without the parent record then you should choose CASCADE mode (this is what we use most of the time)
    While there is nothing wrong with this advice, my own default choice is NO ACTION.
    I prefer to force client programs to consider the appropriateness of deleting a parent record with children.
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    23
    Rep Power
    0
    Could you show me an example of ALTER TABLE syntax to make this change.

    Originally Posted by Jacques1
    Hi,

    depends on what action you set for ON DELETE: NO ACTION, RESTRICT, CASCADE (delete child records), SET DEFAULT or SET NULL

IMN logo majestic logo threadwatch logo seochat tools logo