ALTER TABLE tada_prod.action_6_weekly ADD COLUMN id INT NULL AUTO_INCREMENT UNIQUE AFTER member_id;
works,
so i thought, to add the column as the first column i could do
ALTER TABLE `tada_prod`.`action_6_weekly` ADD COLUMN `id` INT NULL AUTO_INCREMENT UNIQUE BEFORE `code`;
but i get a syntax error, what is the correct syntax?
Extending @php answer, I think the rationale behind not including BEFORE is because all the effects of BEFORE can be easily achieved using AFTER and FIRST
For Ex:
Let's say, initially you have a relational schema like
+----------------------+
| name | age | address |
+----------------------+
and then for some reason you're compelled to add a new column dob(date of birth) just before age, but since BEFORE is not allowed, what you can do instead is insert dob just after the name using AFTER and very well achieve the same effect.
+----------------------------+
| name | dob | age | address |
+----------------------------+
But what if you wanted to insert a new column `id` before `name`?
Since there is no column before name we cannot use AFTER to place the id column. In-order to resolve this, the language designers introduced FIRST which makes the desired id column as the first column of the table.
+---------------------------------+
| id | name | dob | age | address |
+---------------------------------+
Although I personally think AFTER and BEFORE would've made a more intuitive pair.