Alter table in database throwing error in SQL Developer

Question

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?

thatjeffsmith · Accepted Answer · 2021-10-25 17:34:12Z

3

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.

edited Oct 25, 2021 at 17:34

answered Oct 25, 2021 at 17:08

thatjeffsmith

22.7k6 gold badges44 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

thatjeffsmith Over a year ago

agreed, thanks @AlexPoole

happytohelp Over a year ago

perfect!! it worked. also thanks for sharing the information on sql developer edit functionality.

thatjeffsmith Over a year ago

you bet @happytohelp - the GUI can help a lot, but you'll get the most out of it if you observe the back-end code it is generating for you so you can learn the lower level basics of Oracle Database. Good luck!

Collectives™ on Stack Overflow

Alter table in database throwing error in SQL Developer

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related