Results 1 to 11 of 11
  1. #1
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187

    Unanswered: Do we need to index foreign keys?

    Hi,

    Sorry if this question is a bit silly...

    For simplicity, let's say I've the following 2 tables:

    Code:
    CREATE TABLE users (
      id MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
      username VARCHAR(20) NOT NULL DEFAULT '',
    )
    
    CREATE TABLE points (
      member_id MEDIUMINT(8) UNSIGNED NOT NULL,
      points MEDIUMINT(8) UNSIGNED NOT NULL,
      INDEX (member_id),
    )
    Is there a need to index the foreign key "member_id" in the "points" table?

    Thanks in advance

  2. #2
    Join Date
    Mar 2004
    Posts
    480
    Yes you should index it since you will be most likely joining on it.

    You should also actually declare it as a foreign key because right now it isn't one and you could have values for member_id that don't exist as id in your users table.

    also note you need innodb table types to enforce foreign keys. you can declare them in myisam tables but they aren't enforced.

  3. #3
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187
    Thanks, guelphdad

    I wasn't sure because I thought once it's indexed as a Primary Key in the "users" table, it need not be indexed elsewhere.

    I've reproduced my sample tables - in my haste, I forgot about using InnoDB.

    Code:
    CREATE TABLE users (
      id MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
      username VARCHAR(20) NOT NULL DEFAULT '',
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    
    CREATE TABLE points (
      member_id MEDIUMINT(8) UNSIGNED NOT NULL,
      points MEDIUMINT(8) UNSIGNED NOT NULL,
      INDEX (member_id),
      CONSTRAINT FOREIGN KEY (member_id) REFERENCES users (id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    Are the new (albeit reduced) tables correct?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by pearl2
    Are the new (albeit reduced) tables correct?
    they may be correct, but they don't make sense to me

    why aren't they combined into a single table?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187
    Thanks for asking that, r937.

    I find myself wrestling with that question - whether to split the data into different tables or lump everything in one.

    Here I'm splitting because the "users" table is used to contain essential information about the users. The "points" table stores all information related to points - there could be more than one type of points, e.g.

    Code:
    CREATE TABLE points (
      member_id MEDIUMINT(8) UNSIGNED NOT NULL,
      experience_points MEDIUMINT(8) UNSIGNED NOT NULL,
      merit_points MEDIUMINT(8) UNSIGNED NOT NULL,
      conduct_points MEDIUMINT(8) UNSIGNED NOT NULL,
      INDEX (member_id),
      CONSTRAINT FOREIGN KEY (member_id) REFERENCES users (id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    Is that a good reason for splitting the data into different tables?

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by pearl2
    Is that a good reason for splitting the data into different tables?
    actually, no, now it's even worse

    please note that over-simplifying your actual scenario leads to wasted time for everybody

    your first post made sense only if the relationship were intended to eliminate the possibility of a NULL in the points column in a combined table (i.e. a user without any points)

    now we see several different points, declared NOT NULL

    if it made sense to split one possibly NULL attribute off into a one-to-one relationship, it makes far less sense now, where presumably different types of points are all declared as NOT NULL and forced to co-habit in the same row of a one-to-one table

    put all these columns into the main table and let them be NULL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187
    Thanks but I'm confused now

    I've the following tables. Should I be putting all the columns in "preferences" and "quota" into the main table "users"?

    Code:
    CREATE TABLE users (
      id MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
      username VARCHAR(20) NOT NULL DEFAULT '',
      email VARCHAR(80) NOT NULL DEFAULT '',
      password VARCHAR(32) NOT NULL DEFAULT '',
      logged DATETIME,
      logins MEDIUMINT(8) UNSIGNED NOT NULL DEFAULT '0',
      online ENUM('Y','N') NOT NULL DEFAULT 'Y',
      ip VARCHAR(50),
      UNIQUE KEY username (username),
      UNIQUE email (email)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    
    CREATE TABLE preferences (
      member_id MEDIUMINT(8) UNSIGNED NOT NULL DEFAULT '0',
      enable_pm CHAR(1) DEFAULT '1',
      enable_chat CHAR(1) DEFAULT '0',
      enable_newsletters CHAR(1) DEFAULT '1',
      CONSTRAINT FOREIGN KEY (member_id) REFERENCES users (id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    
    CREATE TABLE quota (
      member_id MEDIUMINT(8) UNSIGNED NOT NULL DEFAULT '0',
      job_quota TINYINT(2) UNSIGNED NOT NULL DEFAULT '20',
      vote_quota TINYINT(2) UNSIGNED NOT NULL DEFAULT '5',
      reset DATETIME,
      CONSTRAINT FOREIGN KEY (member_id) REFERENCES users (id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by pearl2
    Should I be putting all the columns in "preferences" and "quota" into the main table "users"?
    that's twice the tables have changed on me, you gonna change them again?

    yes, i don't see why you wouldn't have those columns in the users table

    why did you think you needed them separate?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187
    My apologies on the tables...Looks like I have different problem now than the INDEX question I was asking

    I split the data because I thought it I should put similar things together, so that in the future, I like needed to add a quota-related column, I just add it to the "quota" table, leaving the "users" table intact.

    Is that a good approach?

    If not, am I right to say we only have one table (B) related to another table (the main table A) if it's possible that B may not contain any data at all?

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by pearl2
    I split the data because I thought it I should put similar things together
    that's a good idea -- put all the user stuff together in one table

    there are few reasons why you'd have a one-to-zero-or-one relationship, and i don't see a reason here

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187
    Many thanks for your patience, r397

    I think I got it now.

    So I should put all the columns in "quota" and "preferences" in the main table "users" since there's going to be only one row of each of the columns in "quota" and "preferences" in the "users" table.

    Am I right?

Posting Permissions

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