Results 1 to 4 of 4
  1. #1
    Join Date
    May 2007
    Posts
    2

    Unanswered: My first DB: Indexes question

    Hi guys! Im making my first DB (total newbie just read some online articles) for a little online project I'm doing and i have a few questions? and i'll ask 'em one at a time

    I have MySQL 4.1 on my hosting, im using fabFORCE BD Designer 4 for visual DB design, and im using phpMyAdmin & visual tools from MySQL site to edit tables.

    I have two tables: Companies and Services, each table has autoinc primary "id" key. Each company needs to be able to have any number of services (available from the "services" table). So, from what I understand I need an intermediate table. This DBDesigner tool has "n:m Relation" thingie which, when i connect my two tables, automatically creates the "intermediate" table. This "intermediate" table has two FK fields, one for each "other" tables' id fields (so far all this fits into my understanding of how this should work).

    here's sql code for that table tool generates (OBJECT here is my COMPANY table):
    Code:
    CREATE TABLE ALLSERVICES (
      OBJECT_id INTEGER UNSIGNED NOT NULL,
      SERVICE_id TINYINT UNSIGNED NOT NULL,
      payservice BOOL NULL,
      PRIMARY KEY(OBJECT_id, SERVICE_id),
      INDEX object_id_FKIndex(OBJECT_id),
      INDEX service_id_FKIndex(SERVICE_id)
    );
    in phpMyAdmin I under Indexes for this table i have
    Code:
    Keyname             Type    Cardinality Field 
    PRIMARY             PRIMARY 0           OBJECT_id  
                                            SERVICE_id  
    object_id_FKIndex   INDEX   None        OBJECT_id  
    service_id_FKIndex  INDEX   None        SERVICE_id
    and a warning: PRIMARY and INDEX keys should not both be set for column `OBJECT_id`

    and that is my question - what's that? do i even need this PRIMARY index, maybe i should just delete it? why is it even being created?

    Thanks in advance!

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Moving to MySQL forum
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you needn't worry about the error message, but you definitely want that particular primary key

    in all cases where you use a tool to generate code, you should have an opportunity to intercept the code before putting it into effect

    in this instance, i recommend removing the code in red and inserting the code in blue --
    Code:
    CREATE TABLE ALLSERVICES (
      OBJECT_id INTEGER UNSIGNED NOT NULL,
      SERVICE_id TINYINT UNSIGNED NOT NULL,
      payservice BOOL NULL,
      PRIMARY KEY(OBJECT_id, SERVICE_id),
      INDEX object_id_FKIndex(OBJECT_id),
      INDEX service_id_FKIndex(SERVICE_id,OBJECT_id)
    );
    this will eliminate the redundant index and make the second index more useful

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

  4. #4
    Join Date
    May 2007
    Posts
    2
    Thank you!

Posting Permissions

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