MySQL – Add a Column at Particular Ordinal Position in Table

Sometimes you may need to add a column at a particular ordinal position in the table. In SQL Server there is no option to do it via SQL although you can use Designer from SQL Server Management Studio. In MySQL you can do it as part of ALTER TABLE statement.

Let us consider the following table

CREATE TABLE sales(sales_id INT auto_increment KEY,item_id INT, sales_date DATETIME, sales_amount DECIMAL(12,2));

Suppose you want to add another column named modified_date between item_id and sales_date, you can use the following command

ALTER TABLE sales ADD COLUMN modified_date DATETIME after item_id;

Now the new column named modified_date will be added after item_id. You can check this using the following command

Field		Type	Null	Key	Default	Extra
--------------------------------------------------------------
sales_id	int(11)	NO	PRI		auto_increment
item_id	int(11)	YES			
modified_date	datetime	YES			
sales_date	datetime	YES			
sales_amount	decimal(12,2)	YES

This is sometimes useful. In relational database the order of column in a table should not matter. The database design and modeling should be such that there should not be any dependency on the order the columns are created inside MySQL table. This is the reason, in SQL Server we can’t do this operation via T-SQL and when we do with the help of SSMS, it practically creates and drop the entire table in the background – (a very bad thing).

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

One thought on “MySQL – Add a Column at Particular Ordinal Position in Table

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s