SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    deletion from parent table question

    Groan: having re-read this I think the answer is obvious but I feel a bit unsure.

    I've got a parent table and a child table.

    foreign key constraints are set up correctly I think, using ON DELETE RESTRICT.

    I want to deletion from the parent table to be possible only, if the are no related child records.

    should my delete statement simply rely on the constraint to decide if deletion is to be effected or should I join on the child table and make sure there are no matching records? (Then, if there is none, perform the deletion)

    bazz

  2. #2
    Non-Member
    Join Date
    Apr 2004
    Location
    Miami, FL, USA
    Posts
    448
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    FK delete restriction is default behavior... just ensure that your FKs are properly defined and that you're using InnoDB for your engine (the default MyISAM driver doesn't properly implement FK relationships).

    Example:

    Code:
    CREATE TABLE parent_table (
        id INT NOT NULL AUTO_INCREMENT,
        name VARCHAR(255) NOT NULL,
        CONSTRAINT parent_table_pk PRIMARY KEY (id)
    ) TYPE=InnoDB;
    
    CREATE TABLE child_table (
        id INT NOT NULL AUTO_INCREMENT,
        parent_id INT NOT NULL,
        name VARCHAR(255) NOT NULL,
        CONSTRAINT child_table_pk PRIMARY KEY (id),
        CONSTRAINT child_table_fk_parent_id FOREIGN KEY (parent_id) REFERENCES parent_table(id)
    ) TYPE=InnoDB;
    (Added some constraint names per my convention... I use pk for primary keys, fk_column_name for foreign keys, and ix_column_name for indexes)


Bookmarks

Posting Permissions

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