How to Add New Column to MySQL Table

Adding new column via MySQL Workbench into existing table

I don't know if Stackoverflow is proper place to such question, but...

Click on the table you want to change (using right mouse button) and choose "Alter table" (schemas and tables lists are on the left).

In the opened tab you can add or change columns (GUI is very simple and intuitive).

Don't forget to save changes by clicking "Apply".

MySQL Workbench 6.3: how should I insert a new column into a specific position (in the middle of the table)

In MySQL Workbench you can open the table editor (via the context menu in the schema tree -> Alter table...). On the columns tab you see all currently defined columns. You can add new ones, remove unneeded ones and drag them into the order you want. Then apply the changes. The upcoming wizard with allow you to review of the code which will be sent to the server. Existing data should not be touched (unless you drop a column with data), but it's certainly safer to keep a backup around in case something goes wrong.

Adding multiple columns AFTER a specific column in MySQL

Try this

ALTER TABLE users
ADD COLUMN `count` SMALLINT(6) NOT NULL AFTER `lastname`,
ADD COLUMN `log` VARCHAR(12) NOT NULL AFTER `count`,
ADD COLUMN `status` INT(10) UNSIGNED NOT NULL AFTER `log`;

check the syntax

How to add new column in table as primary key?

Doing this gives ERROR since whenever you add a new column in a table which already has 1 or more rows then the new column will get NULL values in all of its tuples which is contradictory to the rule which says PRIMARY KEY CAN NOT CONTAIN NULL.

Also, if you provide DEFAULT value, then also duplicate entries aren't allowed in the primary key!

So just by adding a new column in a non-empty table by giving default and declaring it primary key at the same time will not work.

Now here comes AUTO_INCREMENT to rescue, add column by incrementing and declarig it as primary key:

ALTER TABLE table_name ADD COLUMN new_column INT AUTO_INCREMENT PRIMARY
KEY ;

This works fine now...

Thanks for asking.

How to add new column to MYSQL table?

your table:

q1 | q2 | q3 | q4 | q5

you can also do

ALTER TABLE yourtable ADD q6 VARCHAR( 255 ) after q5

Adding a new NOT NULL column to an existing Table with data

This piece of SQL is not valid in MySQL:

ALTER TABLE `mytable` ALTER COLUMN newCol BIT NOT NULL;

Instead, consider :

ALTER TABLE `mytable` MODIFY newCol BIT NOT NULL;

Reference : MySQL ALTER TABLE syntax

---

Related Topics