Clever Geek Handbook

Trying to better understand the MySQL ALTER syntax

I am learning MySQL by reading the book "Head First SQL" but I hit a brick wall. I can't seem to get my head down around the code below. In particular, I have a couple of questions.

  • When I try to run all the code as it is on the console (MySQL on Wamp), I get several errors. But when I strip each ALTER command (like ALTER table_name RENAME TO new_table_name; ), most of the code goes through the console without any problem. Why is this? According to the book, all the code should work.

  • One of the errors I get when I copy / paste the code separately is with ORDERING, like FIRST, SECOND, AFTER, etc. Why could this happen?

  • Finally, why do some ALTER commands like RENAME and ADD COLUMN need ALTER TABLE table_name but others like CHANGE or MODIFY COLUMN don't? The book doesn't explain it very well, so I'm curious as to why I'm allowed to do this with some, but not codes.

Could the errors I receive be the result of deprecated code? The book was published in 2007, so I'm not sure if the code is outdated or not.

Any and all advice is greatly appreciated. Thank you for your time!

CODE: ALTER TABLE table_name RENAME TO new_table_name,

ALTER TABLE new_table_name
ADD COLUMN column1 INT NOT NULL AUTO_INCREMENT FIRST, 

ADD PRIMARY KEY(column1), 

ALTER TABLE new_table_name
ADD COLUMN column2 VARCHAR(16) SECOND, 

CHANGE COLUMN column3 new_column3 VARCHAR(20), 
MODIFY COLUMN column4 AFTER column3, 

MODIFY COLUMN column5 SIXTH, 

CHANGE COLUMN column6 DECIMAL(7,2);
+3


source to share


2 answers


The example code you are working from is broken. The keyword FIRST

for ADD COLUMN

, CHANGE COLUMN

and MODIFY COLUMN

is special case; for example, there is no similar keyword SECOND

. Columns outside the first should instead be described as belonging AFTER another_column

.

Also, for some reason the second element ALTER TABLE

was sent to the middle of this statement. There is no such inconsistency in real syntax; it's just a mistake in your book.

Finally, the change MODIFY COLUMN

should include the new column type. (If the goal ALTER

is simply to move the column to a different position in the table, you can specify the current column type.)



It's worth reading the official documentation for the teamALTER TABLE

. It's long but detailed. (And better yet, that's right.)

Here is a revised version of the request with comments. Please note that I have not tested this query on a real table, so I am not sure if it is fully fixed. (In particular, I left the types column4

and column5

how SOME_TYPE

, because I don't know what their type should be.)

ALTER TABLE new_table_name
    ADD COLUMN column1 INT NOT NULL AUTO_INCREMENT FIRST, 
    ADD PRIMARY KEY(column1), 
    ADD COLUMN column2 VARCHAR(16) AFTER column1,  -- no extra ALTER; not SECOND
    CHANGE COLUMN column3 new_column3 VARCHAR(20),
    MODIFY COLUMN column4 SOME_TYPE AFTER new_column3, -- need type, and must
                                                       -- use new column name
    MODIFY COLUMN column5 SOME_TYPE AFTER column4, -- not SIXTH
    CHANGE COLUMN column6 DECIMAL(7,2);
+2


source


  • Use semicolons to separate statements;
  • Keep in mind the statements that depend on the results of the previous one.


0


source






More articles:


All Articles