Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2009
    Posts
    104

    Unanswered: syntax question on creating index

    In a DDL statement creating a foreign-column (within a create table statement), should the ADD INDEX statement go below the column definitions with the contraint clauses?

    All my texts use examples with ALTER TABLE code. Is the syntax the same with a CREATE TABLE statement?

    Code:
    CREATE TABLE thistable
    (
         thistableID            INTEGER     NOT NULL    PRIMARY KEY
         foreignkeynameID   INTEGER     NOT NULL
         nonkeycolumnname DECIMAL(3,2) NULL
    ADD INDEX 
        fkindexname_idx (foreignkeynameID)
    ADD CONSTRAINT 
         foeignkeynameID_fk
                FOREIGN KEY (foreignkeynameID)
                      REFERENCES thattable (foreignkeynameID)
                             ON DELETE RESTRICT
    	                 ON UPDATE  CASCADE
    )

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you can do it in the CREATE statement, or you can do it afterwards, with ALTER TABLE...

    ... but you can not, as you have done there, use the ADD keyword inside the CREATE statement

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

  3. #3
    Join Date
    Feb 2009
    Posts
    104

    redone index key

    So, then, like this?

    Code:
    CREATE TABLE thistable
    (
         thistableID            INTEGER      NOT NULL    PRIMARY KEY
         foreignkeynameID   INTEGER      NOT NULL  INDEX 
                  fkindexname_idx (foreignkeynameID)
         nonkeycolumnname DECIMAL(3,2) NULL 
    ADD CONSTRAINT 
         foeignkeynameID_fk
                FOREIGN KEY (foreignkeynameID)
                      REFERENCES thattable (foreignkeynameID)
                             ON DELETE RESTRICT
    	                 ON UPDATE  CASCADE
    )

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by rbfree
    So, then, like this?
    what happened when you tested that?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2009
    Posts
    104

    somewhat embarrassing answer

    Quote Originally Posted by r937
    what happened when you tested that?
    To be honest, I've been holding out for another text (MySQL Tutorial) that I've ordered so I can get acquainted with MySQL, which I only installed this week. I knew it was only a matter of time before you or someone else asked that question.

    So, in the meantime, I was building the basic txt file with all my create table statements.

    However, I see that it's time to be testing these constructions, so I'll redirect my energies for a couple of days until this text arrives and I've had the chance to digest it. (Should be here today or tomorrow.)

    When I get to that point, I'll answer your question.

  6. #6
    Join Date
    Feb 2009
    Posts
    104

    error

    Quote Originally Posted by r937
    what happened when you tested that?
    Syntax error. Placing the statement directly before the constraint works. That's just as well and it looks less clustered.
    In theory, there is no difference between theory and practice. In practice there is.
    Disputed Origins

    Ninety-three percent of all statistics are made-up on the spot.
    Ancient proverb.

  7. #7
    Join Date
    Mar 2004
    Posts
    480
    your foreign key was fine where it was, your error, as pointed out, was having the word ADD in your constraint.

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    well you could of course not bother with creating your own ddl and use a tool such as MySQL Adminstrator or Heidi to do that for you. Im not knocking the creation of DDL but if theres a tool that'l do it for you why not use it?
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Feb 2009
    Posts
    104

    thanks

    Thanks for all pointers here.

    Re. why I'm writing the DDL is that I want to understand the basics. I'm also playing with the MySQL query/admin tool and HeidiSQL. The learning curve is steep right now so I'm asking lots of questions and dealing with the devil of details. My apologies for this. I'm starting to put it all together, establish some templates, habits, etc.
    In theory, there is no difference between theory and practice. In practice there is.
    Disputed Origins

    Ninety-three percent of all statistics are made-up on the spot.
    Ancient proverb.

Posting Permissions

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