Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2010
    Posts
    4

    Question Unanswered: CREATE TABLE help (unnamed index???)

    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!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by krice View Post
    Does this line actually create an index like the three lines below it?
    yes


    Quote Originally Posted by krice View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2010
    Posts
    4
    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!

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by krice View Post
    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)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2010
    Posts
    4
    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!

  6. #6
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    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

  7. #7
    Join Date
    Jun 2010
    Posts
    4
    Quote Originally Posted by dav1mo View Post
    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.

  8. #8
    Join Date
    Mar 2004
    Posts
    480
    Quote Originally Posted by krice View Post
    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?

Posting Permissions

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