在 Oracle 中的关键字后更改表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8922638/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Alter table after keyword in Oracle
提问by radu florescu
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?
为什么我也不能在 Oracle 中使用 mySql 语法？上述命令适用于 MySql。你能给我一个有效的等价物吗？
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?
我在问是否有任何方法可以在我提供的 Oracle 命令中使用 after 子句？
回答by paxdiablo
Because SQL is a relational algebra. It doesn't care one bit about "where" columns are located within a table, only that they exist.
因为 SQL 是一种关系代数。它一点也不关心表中列的“位置”，只关心它们的存在。
To get it to work in Oracle, just get rid of the afterclause. The Oracle documentation for alter tableis herebut it boils down to:
要让它在 Oracle 中工作，只需去掉after子句即可。对于Oracle文档alter table是在这里，但它归结为：
alter table testTable
add ( column1 number(1) default 0 not null )
There is noafterclause for the alter tablecommand.
有没有after在子句alter table命令。
回答by Ilion
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.
Oracle 不支持在表中间添加列，只支持添加到最后。您的数据库设计和应用程序功能不应依赖于数据库架构中列的顺序。毕竟，您始终可以在 select 语句中指定顺序。
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 col1is your new column and then you specify other columns as needed from your original table. Put the SELECT 0 AS col1at the appropriate place in the order you want.
哪里SELECT 0 AS col1是您的新列，然后您根据需要从原始表中指定其他列。SELECT 0 AS col1按照您想要的顺序将 放在适当的位置。
Afterwards you may want to run an alter table statement on the column to make sure it's the data type you desire.
之后您可能希望在列上运行一个 alter table 语句以确保它是您想要的数据类型。
回答by DrLazer
Try this :
尝试这个 ：
ALTER TABLE testTable ADD column1 NUMBER(1) DEFAULT 0 NOT NULL
