Home » SQL & PL/SQL » SQL & PL/SQL » ALTER TABLE stmt for modified CHECK constraint for Oralce 9.x
icon7.gif  ALTER TABLE stmt for modified CHECK constraint for Oralce 9.x [message #192393] Tue, 12 September 2006 02:46 Go to next message
teamatora
Messages: 8
Registered: September 2006
Junior Member
Hi,
Can you help me in writing a statement to alter the check constraint through ALTER TABLE statement.

The statements are as follows...
CREATE TABLE Table1 (
a NUMBER NOT NULL
CONSTRAINT Valid1
CHECK (a BETWEEN 4 AND 9)
);

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

I want to change the constraint range from 4-9 to 4-12. Can you pls provide me the correct statement for changing it through ALTER TABLE...MODIFY...CONSTRAINT... statement.

Thanks in advance,
~ Prabhakar
Re: ALTER TABLE stmt for modified CHECK constraint for Oralce 9.x [message #192397 is a reply to message #192393] Tue, 12 September 2006 02:58 Go to previous messageGo to next message
Liza79
Messages: 74
Registered: September 2006
Member
You cannot change a constraint through MODIFY CONSTRAINT clause in the way you want. The MODIFY CONSTRAINT clause for ALTER TABLE command is used to change the state of the constraint i.e.
[[NOT] DEFERRABLE] [INITIALLY {IMMEDIATE|DEFERRED}]
       [RELY | NORELY] [USING INDEX using_index_clause]
          [ENABLE|DISABLE] [VALIDATE|NOVALIDATE]


If you want to change the constraint in this way i.e. (4-9 to 4-12) then you will have to drop and recreate the constraint.

Regards,
Liza

icon14.gif  Re: ALTER TABLE stmt for modified CHECK constraint for Oralce 9.x [message #192409 is a reply to message #192397] Tue, 12 September 2006 04:13 Go to previous messageGo to next message
teamatora
Messages: 8
Registered: September 2006
Junior Member
Hi Liza,
Thank you for the information. It is really useful for me Razz .

~ Prabhakar
Re: ALTER TABLE stmt for modified CHECK constraint for Oralce 9.x [message #192419 is a reply to message #192409] Tue, 12 September 2006 05:01 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
teamatora wrote on Tue, 12 September 2006 11:13

Thank you for the information. It is really useful for me Razz .

~ Prabhakar
Prabhakar,
check out the first post in this forum (tips/tricks). It contains other useful resources like the Oracle Online documentation. All syntax schemas (just like the one Liza posted) can be found in there.

MHE
Previous Topic: splliting the string
Next Topic: query a table
Goto Forum:
  


Current Time: Fri Dec 09 19:20:29 CST 2016

Total time taken to generate the page: 0.09831 seconds