sql - 在 SQL Developer 中更改数据库中的表引发错误

Question

我有以下查询来更改我的客户表以添加一列以检查客户是否处于活动状态。

ALTER TABLE COMPANY.CUSTOMER 
    ADD (isActive VARCHAR2(18 CHAR) DEFAULT 'FALSE' NOT NULL) 
        CHECK(isActive in ('TRUE','FALSE'));

我收到以下错误：

RA-01735：无效的 ALTER TABLE 选项
01735。00000 -“无效的 ALTER TABLE 选项”

我试图更改顺序，但仍然没有工作。你能帮我解释为什么它不能改变桌子吗？

另外如何确保插入时 TRUE 或 FALSE 是否始终为大写？

标签： sqloracleconstraintsalter

Answer 1

You can split up the CHECK constraint from the ADD COLUMN for one route...

/* drop table customer_so purge; */

create table customer_so (id integer, name varchar2(50));

ALTER TABLE CUSTOMER_SO 
ADD (ISACTIVE VARCHAR2(20) DEFAULT 'FALSE' );

ALTER TABLE CUSTOMER_SO
ADD CONSTRAINT CUSTOMER_SO_CHK1 CHECK 
(ISACTIVE IN ('TRUE', 'FALSE'))
ENABLE;

Or, like you had, all in one go -

/* drop table customer_so purge; */

create table customer_so (id integer, name varchar2(50));

ALTER TABLE CUSTOMER_SO 
ADD (ISACTIVE VARCHAR2(20) DEFAULT 'FALSE' constraint CUSTOMER_SO_CHK1 CHECK 
(ISACTIVE IN ('TRUE', 'FALSE')));

So basically end of the day, you're missing the 'CONSTRAINT' keyword.

Since you tagged oracle-sqldeveloper, you should know the EDIT TABLE ddialog lets you click through these changes, and will generate the DDL for you -

Finally, by default, strings in Oracle are case-sensitive, so your check constraint will work as desired.