Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2007
    Location
    London
    Posts
    117

    Unanswered: Delete on Cascade?

    Hello all,

    I have the following two tables:
    Code:
    CREATE TABLE `groups` (
      `groupsID` MEDIUMINT(9) NOT NULL AUTO_INCREMENT,
      `groupName` VARCHAR(255) COLLATE latin1_swedish_ci NOT NULL DEFAULT '',
      `dateCreated` DATE NOT NULL,
      PRIMARY KEY (`groupsID`)
    
    )ENGINE=InnoDB
    AUTO_INCREMENT=3 CHARACTER SET 'latin1' COLLATE 'latin1_swedish_ci'
    COMMENT='InnoDB free: 3072 kB';
    Code:
    CREATE TABLE `groupsettings` (
      `typeV` TINYINT(1) NOT NULL,
      `typeA` TINYINT(1) NOT NULL,
      `typeT` TINYINT(1) NOT NULL,
      `imagePath` VARCHAR(250) COLLATE latin1_swedish_ci DEFAULT NULL,
      `groupsID` INTEGER(11) NOT NULL AUTO_INCREMENT,
      PRIMARY KEY (`groupsID`),
      UNIQUE KEY `groupsID` (`groupsID`)
    
    )ENGINE=InnoDB
    AUTO_INCREMENT=1 CHARACTER SET 'latin1' COLLATE 'latin1_swedish_ci'
    COMMENT='InnoDB free: 3072 kB';;
    What i want to happen is that if i delete a group from the groups table i want the related groupssettings row to also be deleted automatically. How do i do this? Something to do with cascade?

    Thanks all

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, something to do with the cascade, specifically a foreign key

    note that in your second table, you have a primary key and then you have a unique key on the same column -- this is redundant, since a primary key is a unique key

    if you want the groupsettings table to be related to the groups table, you should not make groupsID in groupsettings an auto_increment

    you will need to make groupsID in groupsettings the same DATATYPE as groupsID in groups

    and it would be unusual for groupsID in groupsettings to be unique -- if it is, then it would be in a one-to-one relationship, and then why would you have groupsettings as a separate table

    so then groupsID in groupsettings would need an index, and also be declared a foreign key, and then of course groupsettings would need its own primary key as well

    does any of that help?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2004
    Location
    Europe->Sweden->Stockholm
    Posts
    71
    Yes. The "groupsID" column in groupsettings is a foreign key. The exact syntax can be found in the MySQL manual, section 13.2.6.4. Remember that the columns in the two tables must be of the exact same type; if they are not, you will scratch your head for a while since MySQL does not always report very helpful errors.

    EDIT: Noticed that r937 also replied to this. I did not add anything at all except the link to the MySQL manual. It seems that r937's eyes (or perhaps mind) is also sharper as he noticed a lot of things that I simply glossed over.

  4. #4
    Join Date
    Jun 2007
    Location
    London
    Posts
    117
    Quote Originally Posted by r937
    yes, something to do with the cascade, specifically a foreign key

    note that in your second table, you have a primary key and then you have a unique key on the same column -- this is redundant, since a primary key is a unique key

    if you want the groupsettings table to be related to the groups table, you should not make groupsID in groupsettings an auto_increment

    you will need to make groupsID in groupsettings the same DATATYPE as groupsID in groups

    and it would be unusual for groupsID in groupsettings to be unique -- if it is, then it would be in a one-to-one relationship, and then why would you have groupsettings as a separate table

    so then groupsID in groupsettings would need an index, and also be declared a foreign key, and then of course groupsettings would need its own primary key as well

    does any of that help?
    yes that was a lot of help!

    The boldened line above interested me. This will be a one-to-one relationship: so would you say just have it all in one table?

    I seperated it out because i plan to have many more columns and the table groups is in relation with another table.

    As you can see i am little rusty with db design, emphasis on "a little".

  5. #5
    Join Date
    Apr 2004
    Location
    Europe->Sweden->Stockholm
    Posts
    71
    If it is valid for a "group" to have no "settings", then separating them seems like a good idea, especially if you do not always need to retrieve settings. If almost all groups have settings and you almost always need them when fetching groups, having them in one table seems more convenient.

  6. #6
    Join Date
    Jun 2007
    Location
    London
    Posts
    117
    Quote Originally Posted by snorp
    If it is valid for a "group" to have no "settings", then separating them seems like a good idea, especially if you do not always need to retrieve settings. If almost all groups have settings and you almost always need them when fetching groups, having them in one table seems more convenient.
    Ah, i see. This is the kind of foresight i need!

    Well, in my case, there will be many cases where they will be called together. And only some cases when they will be called sepertaly.

    Also the table groups will have relationhips with 2/3 other tables. If the two tables are combined [groups and groupsettings] any other relation calls with the combined table and other tables will be quite strenuous on the server since it contains a lot of columns and data - right?

    What would you say would be the best way to have it: seperate or not?

    EDIT: Some groups will have some settings but it will be never the case where no group has no settings. There will be at least 2 fields of the 5 present.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by compsci
    If the two tables are combined [groups and groupsettings] any other relation calls with the combined table and other tables will be quite strenuous on the server since it contains a lot of columns and data - right?
    no, this is not right

    specifically, the "quite strenous" part is not right, as well as "a lot of columns"

    join speed is not related to the number of columns, since your query's SELECT clause would not include columns it doesn't need

    Quote Originally Posted by compsci
    There will be at least 2 fields of the 5 present.
    this makes me wonder why some of the fields would not be present for a particular group, and therefore whether this table should perhaps be normalized

    what are the V A T columns for?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jun 2007
    Location
    London
    Posts
    117
    Quote Originally Posted by r937
    join speed is not related to the number of columns, since your query's SELECT clause would not include columns it doesn't need
    What about the UPDATE or INSERT?

    Quote Originally Posted by r937
    this makes me wonder why some of the fields would not be present for a particular group, and therefore whether this table should perhaps be normalized

    what are the V A T columns for?
    Some of them are not present since the values are from checkboxes which store users choices.

    Which setup is better do you think?

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i would use one table only

    right now you have 3 options and one url

    even if this balloons to seventeen options and nine other fields, i would still use one table

    primary key is groupid, and you should have no trouble whatsoever letting optional fields be NULL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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