syntax question on creating index

rbfree · #1 (**permalink**) 03-19-09, 14:00

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
)

r937 · #2 (**permalink**) 03-19-09, 14:17

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

rbfree · #3 (**permalink**) 03-19-09, 14:35

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
)

r937 · #4 (**permalink**) 03-19-09, 14:37

Quote:

Originally Posted by rbfree

So, then, like this?

what happened when you tested that?

rbfree · #5 (**permalink**) 03-19-09, 14:54

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.

rbfree · #6 (**permalink**) 03-24-09, 13:45

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.

guelphdad · #7 (**permalink**) 03-25-09, 19:53

your foreign key was fine where it was, your error, as pointed out, was having the word ADD in your constraint.

healdem · #8 (**permalink**) 03-25-09, 20:04

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?

rbfree · #9 (**permalink**) 03-25-09, 22:26

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech