Command Not Properly Ended

[Dario1984]

Hi,

I'm just starting out in the world of SQL and I wrote some code and got an error like this :
SQL>
SQL> ALTER TABLE Book
2 MODIFY CONSTRAINT Book_CHK1
3 (topic IN ('ART', 'ARCHITECTURE', 'BIOGRAPHY', 'CHILDREN', 'COMPUTER',
4 'COOKING', 'FOOD AND WINE', 'GARDENING', 'HEALTH', 'HISTORY', 'GEOGRAPHY',
5 'SCIENCE','RELIGION', 'ROMANCE', 'SPORT', 'OTHER' ));
(topic IN ('ART', 'ARCHITECTURE', 'BIOGRAPHY', 'CHILDREN', 'COMPUTER',
*
ERROR at line 3:
ORA-00933: SQL command not
properly ended

Could anyone who has experinced this tell me what's wrong ? If I put it all on one like without Enter characters, it still doesn't work.

 

[hoinz]

As your constraint seems to be a check constraint, I think there should be keyword check.

(check topic IN ('ART', 'ARCHITECTURE', 'BIOGRAPHY', 'CHILDREN', 'COMPUTER',

hope this helps

 

[hoinz]

In addition/correction to my previous post:
I got the syntax wrong, correct is:

check (topic IN ('ART', 'ARCHITECTURE', 'BIOGRAPHY', 'CHILDREN', 'COMPUTER',

And then you cannot modify a constraint this way. You only may modify some states of a constraint.
For changing the check condition itself you have do drop and create it.

hope this helps at last

 

[Dario1984]

Thanks, I missed the CHECK.

However, it doesn't get rid of the error ? Any other ideas ?

Maybe it's becaue I'm trying to add 'GARDENING' to the already existing condition ? Do I have to delete the condition and re-write it ?

 

[SantaMufasa]

Dario,

I believe you may have missed Hoinz's correct observation:

...you cannot modify a constraint this way...For changing the check condition itself you have drop and (re)create it.

Here are the scenarios that show both the problem and the solution:

Section 1 -- Illegal syntax (matching your original assertion).

***************************************************************************************
SQL> ALTER TABLE Book
  2  MODIFY CONSTRAINT Book_CHK1
  3  CHECK (topic IN ('ART', 'ARCHITECTURE', 'BIOGRAPHY', 'CHILDREN', 'COMPUTER',
  4  'COOKING', 'FOOD AND WINE', 'GARDENING', 'HEALTH', 'HISTORY', 'GEOGRAPHY',
  5  'SCIENCE','RELIGION', 'ROMANCE', 'SPORT', 'OTHER' ));
CHECK (topic IN ('ART', 'ARCHITECTURE', 'BIOGRAPHY', 'CHILDREN', 'COMPUTER',
*
ERROR at line 3:
ORA-00933: SQL command not properly ended

Section 2 -- Legal syntax:

SQL> ALTER TABLE Book DROP CONSTRAINT Book_CHK1;

Table altered.

SQL> ALTER TABLE Book ADD CONSTRAINT Book_CHK1
  2      CHECK (topic IN ('ART', 'ARCHITECTURE', 'BIOGRAPHY', 'CHILDREN', 'COMPUTER',
  3                       'COOKING', 'FOOD AND WINE', 'GARDENING', 'HEALTH', 'HISTORY',
  4                       'GEOGRAPHY','SCIENCE','RELIGION', 'ROMANCE', 'SPORT',
  5                       'OTHER' ));

Table altered.

Let us know if this resolves your need.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[Dario1984]

Oh sorry, I didn't see it. Yeah, it works good.

 

[SantaMufasa]

Dario, "if it works well", then Hoinz deserves a Purple Star from your clicking 'Thank Hoinz for this valuable post', right?

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.