CODEWITHSUNDEEP
mysqlalter-table
Hailwood
Hailwood

Reputation: 92591

alter table add ... before `code`?

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?

Upvotes: 13

Views: 23851

Answers (4)

iCantC
iCantC

Reputation: 3180

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.

Upvotes: 0

php
php

Reputation: 4425

You can add column only after particular field or at first not before. The mysql query for add column after particular filed is:
ALTER TABLE table_name ADD COLUMN column_name VARCHAR(30) AFTER column_name

Upvotes: 10

mnrmon
mnrmon

Reputation: 11

Actually,

alter table table_name ADD column_name VARCHAR(12) NOT NULL BEFORE specific_column_name;

This command is not allowed in mySQL syntax. If you use it I think you get

" ERROR 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'before specific_column_name' at line 1 " message.

You can try:

ALTER TABLE table_name ADD column_name VARCHAR(12) NOT NULL FIRST;

Upvotes: 1

Ben Hoffstein
Ben Hoffstein

Reputation: 103345

ALTER TABLE `tada_prod`.`action_6_weekly`
ADD COLUMN `id` INT NULL AUTO_INCREMENT UNIQUE FIRST;

Upvotes: 23

Related Questions