Home » SQL & PL/SQL » SQL & PL/SQL » how to use between
how to use between [message #125479] Sun, 26 June 2005 20:06 Go to next message
dai_lo
Messages: 30
Registered: June 2005
Member
Hi all,

I created a table which contained an attribute mgrphone_ext number(3).

later, i was requested to add a condition to check if mgrphone_ext is in range 200 to 300.


the following are the incorrect sql script that i tried to add the condition in the mgrphone_txt

alter table manager add constraint mgrphone_ext_check Check (mgrphone_ext between 200 and 300);

alter table manager modify (mgrphone_ext between 200 and 300);

please let me know what i did wrong

thanks,
Re: how to use between [message #125489 is a reply to message #125479] Sun, 26 June 2005 22:05 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
The first one that you tried was correct, as demonstrated below. What caused you to think it was wrong? Did you receive some sort of error message or strange results?

scott@ORA92> create table manager (mgrphone_ext number(3))
  2  /

Table created.

scott@ORA92> alter table manager add constraint mgrphone_ext_check
  2  Check (mgrphone_ext between 200 and 300)
  3  /

Table altered.

scott@ORA92> insert into manager (mgrphone_ext) values (200)
  2  /

1 row created.

scott@ORA92> insert into manager (mgrphone_ext) values (300)
  2  /

1 row created.

scott@ORA92> insert into manager (mgrphone_ext) values (199)
  2  /
insert into manager (mgrphone_ext) values (199)
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.MGRPHONE_EXT_CHECK) violated


scott@ORA92> insert into manager (mgrphone_ext) values (301)
  2  /
insert into manager (mgrphone_ext) values (301)
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.MGRPHONE_EXT_CHECK) violated


scott@ORA92> select * from manager
  2  /

MGRPHONE_EXT
------------
         200
         300

scott@ORA92>


Re: how to use between [message #125493 is a reply to message #125489] Mon, 27 June 2005 00:18 Go to previous messageGo to next message
dai_lo
Messages: 30
Registered: June 2005
Member
HERE is the error message i got
ERROR at line 1:
ORA-02293: cannot validate (CE301A03.MGRPHONE_EXT_CHECK) - check constraint
violated
Re: how to use between [message #125497 is a reply to message #125493] Mon, 27 June 2005 01:02 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

dai_lo wrote on Mon, 27 June 2005 00:48

HERE is the error message i got
ERROR at line 1:
ORA-02293: cannot validate (CE301A03.MGRPHONE_EXT_CHECK) - check constraint
violated


This is simply becoz in you table, there already exists some records the violates the new check constraint.
ie,
records with mgrphone_ext less than 200 or mgrphone_ext greater than 300.
They should never be present for validating a new check constraint

rajuvan
Re: how to use between [message #392150 is a reply to message #125497] Mon, 16 March 2009 11:21 Go to previous message
brwndba
Messages: 1
Registered: March 2009
Location: DALLAS
Junior Member
The table already have data , so you can create constraint like this .
ALTER TABLE tblname ENABLE NOVALIDATE CONSTRAINT constraint_name

it works.
Previous Topic: Oracle10g
Next Topic: Query regarding time difference and time difference problem (merged multiple threads)
Goto Forum:
  


Current Time: Wed Dec 07 20:34:54 CST 2016

Total time taken to generate the page: 0.24129 seconds