CREATE TABLE help (unnamed index???)

krice · #1 (**permalink**) 06-03-10, 02:51

Hello, I need help understanding what 'index (foreignkey)' does below:

Code:

CREATE TABLE user (id integer not null primary key auto_increment,
    email varchar(255) not null,
    entered datetime,
    modified timestamp,
    uniqid varchar(255),
    unique (email),
    password varchar(255),
    foreignkey varchar(100),
    index (foreignkey),
    index idx_uniqid (uniqid),
    index emailidx (email),
    index enteredindex (entered) )

As you can see, the last four lines are all in the form: index index-name (column-name)

Except the fourth line up 'index (foreignkey)' does not have any "index-name".

Does this line actually create an index like the three lines below it? Or, does it perform some other function? If it creates an index, how is it there is no name?

I am trying to understand what this one line does. Much thanks!

r937 · #2 (**permalink**) 06-03-10, 06:02

Quote:

Originally Posted by krice

Does this line actually create an index like the three lines below it?

yes

Quote:

Originally Posted by krice

If it creates an index, how is it there is no name?

the user-supplied index name is optional

do a SHOW CREATE TABLE for this table to see what name the index gets if you don't supply one

krice · #3 (**permalink**) 06-03-10, 11:40

Interesting. I would think for an index to be useful you'd have to be able to refer to it by name. Need to learn more...

MS-SQL requires a name so this syntax was confusing to me, as well as the:

unique (email)

which adds a 'unique' constraint, as opposed to

unique idx_email (email)

which adds an index, I think???

Still confused, but getting better. Thanks for all help!

r937 · #4 (**permalink**) 06-03-10, 11:45

Quote:

Originally Posted by krice

unique idx_email (email)

which adds an index, I think???

that's correct, a unique index

all unique constraints are implemented "under the covers" as unique indexes (i believe this is the case in sql server too)

krice · #5 (**permalink**) 06-03-10, 12:49

You've been greatly helpful! I'm actually porting the above CREATE TABLE over to MS-SQL which doesn't apparently allow unnamed indexes--this I suppose I can just use a "dummy" index name like so:

Code:

CREATE INDEX dummy (foreignkey)

I'm still unclear on why an unnamed index is useful in MySQL. Gee, maybe I should read a book on the subject!!

The MS-SQL syntax for a unique constraint is a bit different (and done in the column definition):

Code:

CREATE TABLE x (email varchar(255) not null UNIQUE)

I think a unique constraint in SQL Server is different from a unique index which is:

Code:

CREATE UNIQUE INDEX name ON table (email)

Thanks again!

dav1mo · #6 (**permalink**) 06-03-10, 13:53

actually, in any database an index name is useless to anyone but the DAB team. You are not going to select data from an index. Even if you have index only access on a particular query, it is from the table that you query. The DBA staff have a different picture of the database and would on occasion need to know the name of an index.
Dave

krice · #7 (**permalink**) 06-03-10, 18:07

Quote:

Originally Posted by dav1mo

actually, in any database an index name is useless to anyone but the DAB team. You are not going to select data from an index.

Thanks Dave. I suppose that just leaves me wondering why the code above names three of the indexes but not the fourth.

Code:

    index (foreignkey),
    index idx_uniqid (uniqid),
    index emailidx (email),
    index enteredindex (entered)

I will provide a "dummy" name for MS-SQL and see if anything breaks, then! Thanks to all.

guelphdad · #8 (**permalink**) 06-04-10, 10:03

Quote:

Originally Posted by krice

Thanks Dave. I suppose that just leaves me wondering why the code above names three of the indexes but not the fourth.

Sloppy coding practices maybe?

Internet Services

Web Development

Digital Marketing

Consumer Tech