Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 9 of 9
Thread: Indexes with Same Name
06-09-2013, 08:40 PM #1
Indexes with Same Name
What happens in MySQL if you have two different Indexes - in two separate Tables - with the same name?
I have read conflicting things online regarding this topic...
06-10-2013, 12:03 AM #2
In MySQL, at least, there's no problem.
May not be true in all databases.Be yourself. No one else is as qualified.
06-10-2013, 12:51 AM #3
06-10-2013, 02:40 AM #4
Oh, yes. And there's no reason that the same name has to index the same *type* in different tables, for that matter.Be yourself. No one else is as qualified.
06-10-2013, 03:36 PM #5
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...
06-10-2013, 07:15 PM #6
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.
CREATE TABLE users ( userid INT AUTO_INCREMENT PRIMARY KEY, ... ) CREATE TABLE products ( prodid INT AUTO_INCREMENT PRIMARY KEY, ... )
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.Be yourself. No one else is as qualified.
06-10-2013, 07:36 PM #7
Have to disagree on this one...
I take this approach...
id slug heading author
id username first_name last_name location
It is redundant to say MEMBER.member_id
id article_id member_id
So anytime you are looking at FK fields, you can easily see the Parent Table.Key combination.
ARTICLE.id -||-----|<- ARTICLE_THREAD.article_id MEMBER.id -||-----|<- ARTICLE_THREAD.member_id
Just as easy to read - in my opinion - and more streamlined...
06-10-2013, 08:58 PM #8
MySQL disagrees with you.
MySQL allows something called a "NATURAL JOIN":
SELECT list, of, fields FROM table1 NATURAL JOIN table2
SELECT P.productname, F.feature FROM products AS P NATURAL JOIN productFeatures AS F
SELECT P.productname, F.feature FROM products AS P INNER JOIN productFeatures AS F ON P.productid = F.productid
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.Be yourself. No one else is as qualified.
06-10-2013, 09:33 PM #9