Alter table in database throwing error in SQL Developer-CodePudding

I have the following query to alter my customer table to add a column for checking if a customer is active or not.

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

I get the following error:

RA-01735: invalid ALTER TABLE option
01735. 00000 - "invalid ALTER TABLE option"

I tried to change the order and still did not work. can you help me with why it is failing to alter the table?

Also how to ensure if TRUE or FALSE is always uppercase when inserting?

CodePudding user response：

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.