Home » SQL & PL/SQL » SQL & PL/SQL » Problem with ALTER TABLE...MODIFY CONSTRAINT...
Problem with ALTER TABLE...MODIFY CONSTRAINT... [message #198196] Mon, 16 October 2006 02:39 Go to next message
teamatora
Messages: 8
Registered: September 2006
Junior Member
Hi,
I have the following information in my database (Oracle 9i) ...
CREATE TABLE Table1
(
a NUMBER NOT NULL CONSTRAINT Valid1 CHECK (a BETWEEN 4 AND 9)
);



ALTER TABLE Table1
ADD CONSTRAINT XPKTable1 PRIMARY KEY (a);

and now I want to change the constraint range from 4-9 to 4-12. Please give me the correct statement.

I've tried...
SQL> ALTER TABLE Table1
2 MODIFY CONSTRAINT Valid1 CHECK (a BETWEEN 4 AND 12);
MODIFY CONSTRAINT Valid1 CHECK (a BETWEEN 4 AND 12)
*
ERROR at line 2:
ORA-00933: SQL command not properly ended

Please help me change the table...

Regards
~ Prabhakar
Re: Problem with ALTER TABLE...MODIFY CONSTRAINT... [message #198203 is a reply to message #198196] Mon, 16 October 2006 02:58 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
You cannot modify a constraint that way. With Modify constraint, you can only change the constraint state (enabled, disabled, deferrred, validate, ...) but not the constraint itself. You'll have to drop it and add it again:
CREATE TABLE Table1
(
a NUMBER NOT NULL CONSTRAINT Valid1 CHECK (a BETWEEN 4 AND 9)
)
/
ALTER TABLE Table1
ADD CONSTRAINT XPKTable1 PRIMARY KEY (a);


ALTER TABLE Table1 DROP CONSTRAINT Valid1;
ALTER TABLE Table1 ADD  CONSTRAINT Valid1 CHECK (a BETWEEN 4 AND 12);


DROP TABLE table1
/


MHE
Re: Problem with ALTER TABLE...MODIFY CONSTRAINT... [message #198218 is a reply to message #198196] Mon, 16 October 2006 03:50 Go to previous messageGo to next message
teamatora
Messages: 8
Registered: September 2006
Junior Member
Hi Maaher,
Thanks for the reply. The same worked for me too. But I was referring to the Oracle 9i reference manual, in which, the syntax diagram for ALTER TABLE statement states that CHECK can be used with MODIFY CONSTRAINT clauses. Can you please provide me some more information, in which cases is this valid.

Please see the Oracle 9i SQL reference that I've referred...

~ Prabhakar

[Updated on: Mon, 16 October 2006 03:57]

Report message to a moderator

Re: Problem with ALTER TABLE...MODIFY CONSTRAINT... [message #198222 is a reply to message #198218] Mon, 16 October 2006 04:01 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Oracle9i Database Concepts Release 2 (9.2) Part Number A96524-01
You can use the MODIFY CONSTRAINT clause of the ALTER TABLE statement to change the following constraint states:
  • DEFERRABLE or NOT DEFERRABLE
  • INITIALLY DEFERRED or INITIALLY IMMEDIATE
  • RELY or NORELY
  • USING INDEX ...
  • ENABLE or DISABLE
  • VALIDATE or NOVALIDATE
  • EXCEPTIONS INTO ...



Oracle9i SQL Reference Release 2 (9.2) Part Number A96540-02
The MODIFY CONSTRAINT clause lets you change the state of an existing constraint.
{ ADD { out_of_line_constraint
        [ out_of_line_constraint ]...
      | out_of_line_REF_constraint
      }
| MODIFY { CONSTRAINT constraint
         | PRIMARY KEY
         | UNIQUE (column)
           [, UNIQUE (column) ]...
         }
            constraint_state
| RENAME CONSTRAINT old_name TO new_name
| drop_constraint_clause
}


Oracle9i SQL Reference Release 2 (9.2) Part Number A96540-02
constraint_state :=
[ [[NOT] DEFERRABLE] [INITIALLY { IMMEDIATE | DEFERRED }]
| [INITIALLY { IMMEDIATE | DEFERRED }] [[NOT] DEFERRABLE]
]
[ RELY | NORELY ] [using_index_clause] [ ENABLE | DISABLE ] 
[ VALIDATE | NOVALIDATE ] [exceptions_clause]


I haven't found anything in the 9i Reference guide backing you up. Can you quote from it? Look again at the syntax diagrams.

MHE

icon14.gif  Re: Problem with ALTER TABLE...MODIFY CONSTRAINT... [message #198246 is a reply to message #198196] Mon, 16 October 2006 04:58 Go to previous message
teamatora
Messages: 8
Registered: September 2006
Junior Member
Hi,
Thanks for the detailed explanation. All my doubts got clarified with this.

Once again thank you.

~ Prabhakar
Previous Topic: sql query
Next Topic: escape clause
Goto Forum:
  


Current Time: Wed Dec 07 12:27:02 CST 2016

Total time taken to generate the page: 0.11496 seconds