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);
source to share
The example code you are working from is broken. The keyword
is special case; for example, there is no similar keyword
. Columns outside the first should instead be described as belonging
Also, for some reason the second element
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
should include the new column type. (If the goal
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 team
. 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
, 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);
source to share