CODEWITHSUNDEEP
sqloracle-database
johnstok
johnstok

Reputation: 98210

Why does this 'modify table' statement fail?

I'm trying to add a 'not null' constraint to a column in Oracle 9.

ALTER TABLE user_roles modify group_id varchar2(36 char) set not null;

However, the operation fails with an odd error:

Error report:
SQL Error: ORA-12987: cannot combine drop column with other operations
12987. 00000 -  "cannot combine drop column with other operations"
*Cause:    An attempt was made to combine drop column with other
           ALTER TABLE operations.
*Action:   Ensure that drop column is the sole operation specified in
           ALTER TABLE.

Any ideas why this is failing?

Upvotes: 1

Views: 983

Answers (3)

APC
APC

Reputation: 146179

I'm trying to add a 'not null' constraint to a column in Oracle 9.

If you are really trying jsut to make the column NOT NULL (i.e. you don't want to change the datatype at the same time) you just need to 

ALTER TABLE user_roles modify not null;

Upvotes: 0

johnstok
johnstok

Reputation: 98210

It turns out the syntax of the above statement is wrong. It should be:

ALTER TABLE user_roles modify group_id varchar2(36 char) not null;

Still, the presence of an erroneous 'set' leads to a very odd error!

Upvotes: 0

Quassnoi
Quassnoi

Reputation: 425251

Remove set:

ALTER TABLE user_roles modify group_id varchar2(36 char) not null

And yes, Oracle's errors can be very misleading.

Upvotes: 2

Related Questions