Alter table after keyword in Oracle

Question

ALTER TABLE testTable ADD column1 NUMBER(1) DEFAULT 0 NOT NULL AFTER column2;

Why can't I use mySql syntax in Oracle too? The above command works in MySql. Can you give me an equivalent that works?

---

Error report:
SQL Error: ORA-01735: invalid ALTER TABLE option
01735. 00000 -  "invalid ALTER TABLE option"

---

I am asking if there is any way to use after clause in Oracle command that I provided?

Answer 1

Because SQL is a relational algebra. It doesn't care one bit about "where" columns are located within a table, only that they exist.

To get it to work in Oracle, just get rid of the after clause. The Oracle documentation for alter table is here but it boils down to:

alter table testTable
    add ( column1 number(1) default 0 not null )

There is no after clause for the alter table command.

Answer 2

Try this :

ALTER TABLE testTable ADD column1 NUMBER(1) DEFAULT 0 NOT NULL

Answer 3

Oracle does not support adding columns in the middle of a table, only adding them to the end. Your database design and app functionality should not depend on the order of columns in the database schema. You can always specify an order in your select statement, after all.

However if for some reason you simply must have a new column in the middle of your table there is a work around.

CREATE TABLE tab1New AS SELECT 0 AS col1, col1 AS col2 FROM tab1;
DROP TABLE tab1 PURGE;
RENAME tan1New to tab1;

Where the SELECT 0 AS col1 is your new column and then you specify other columns as needed from your original table. Put the SELECT 0 AS col1 at the appropriate place in the order you want.

Afterwards you may want to run an alter table statement on the column to make sure it's the data type you desire.