Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 9 of 9
  1. #1
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,075
    Thanks
    26
    Thanked 0 Times in 0 Posts

    Indexes with Same Name

    What happens in MySQL if you have two different Indexes - in two separate Tables - with the same name?

    For example...

    customer
    idx_u_id


    order
    idx_u_id


    I have read conflicting things online regarding this topic...

    Sincerely,


    Debbie

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,216
    Thanks
    75
    Thanked 4,344 Times in 4,310 Posts
    In MySQL, at least, there's no problem.

    May not be true in all databases.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,075
    Thanks
    26
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    In MySQL, at least, there's no problem.

    May not be true in all databases.
    So the "namespace" or whatever you'd call it is clearly separate between Tables?


    Debbie

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,216
    Thanks
    75
    Thanked 4,344 Times in 4,310 Posts
    Oh, yes. And there's no reason that the same name has to index the same *type* in different tables, for that matter.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,075
    Thanks
    26
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    Oh, yes. And there's no reason that the same name has to index the same *type* in different tables, for that matter.
    Good to know.

    Usually the Index Name isn't an issue as far as being unique, however, since I adopted my "All Tables shall have an 'id' AutoIncrement field" policy - per some of what we discussed a few weeks ago - I was concerned this would create an issue, since every table will have...

    idx_u_id


    Thanks,


    Debbie

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,216
    Thanks
    75
    Thanked 4,344 Times in 4,310 Posts
    Well, of course I never use id as a primary key column name. I always include part of the table name in my primary key columns.

    Exaxmples:
    Code:
    CREATE TABLE users (
        userid INT AUTO_INCREMENT PRIMARY KEY,
        ...
    )
    
    CREATE TABLE products (
        prodid INT AUTO_INCREMENT PRIMARY KEY,
        ...
    )
    and so on.

    I do this so that my foreign keys can use the same names, thus making the PK/FK relationships both more obvious and more readable.

    But that's up to you.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #7
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,075
    Thanks
    26
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    Well, of course I never use id as a primary key column name. I always include part of the table name in my primary key columns.

    Exaxmples:
    Code:
    CREATE TABLE users (
        userid INT AUTO_INCREMENT PRIMARY KEY,
        ...
    )
    
    CREATE TABLE products (
        prodid INT AUTO_INCREMENT PRIMARY KEY,
        ...
    )
    and so on.

    I do this so that my foreign keys can use the same names, thus making the PK/FK relationships both more obvious and more readable.

    But that's up to you.

    Have to disagree on this one...

    I take this approach...

    ARTICLE
    Code:
    id
    slug
    heading
    author

    MEMBER
    Code:
    id
    username
    first_name
    last_name
    location
    All of the Fields above are adjectives to the Table which is the noun (e.g. MEMBER.location).

    It is redundant to say MEMBER.member_id


    ARTICLE_THREAD
    Code:
    id
    article_id
    member_id
    Notice how in my Junction Table my Foreign Key fields are a concatenation of the Parent Entity (i.e. "article" and "member") and Field itself (i.e. "id and "id")?

    So anytime you are looking at FK fields, you can easily see the Parent Table.Key combination.


    Relationships:
    Code:
    ARTICLE.id -||-----|<- ARTICLE_THREAD.article_id
    
    MEMBER.id -||-----|<- ARTICLE_THREAD.member_id

    Just as easy to read - in my opinion - and more streamlined...

    Sincerely,


    Debbie

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,216
    Thanks
    75
    Thanked 4,344 Times in 4,310 Posts
    MySQL disagrees with you.

    MySQL allows something called a "NATURAL JOIN":

    Code:
    SELECT list, of, fields
    FROM table1 NATURAL JOIN table2
    So, if you used my scheme, you would do something like
    Code:
    SELECT P.productname, F.feature
    FROM products AS P NATURAL JOIN productFeatures AS F
    and if, say, you used productid as the PK in products and the FK in productFeatures, MySQL would automatically make the connection for you, just as if you had coded
    Code:
    SELECT P.productname, F.feature
    FROM products AS P INNER JOIN productFeatures AS F
    ON P.productid = F.productid
    So MySQL feels that the "natural" thing to do is match up fields by name.

    Specifically:
    The NATURAL [LEFT] JOIN of two tables is defined to be semantically equivalent to an INNER JOIN or a LEFT JOIN with a USING clause that names all columns that exist in both tables.
    **********

    Anyway, you say poh-tay-toh and I say french fries and either one works. I just like grease more than you do.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #9
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,075
    Thanks
    26
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    Anyway, you say poh-tay-toh and I say french fries and either one works. I just like grease more than you do.
    *LOL*

    Yeah, I prefer being verbose when it comes to queries. (Mind you, not "inefficient", just "explicit"...)

    I've always felt the "long-hand" of a proper INNER JOIN is much easier to read than anything "NATURAL", but that's just me.

    Thanks,


    Debbie


  •  

    Posting Permissions

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