Home » SQL & PL/SQL » SQL & PL/SQL » Reg Check constraint over a particular column (Oracle 10g)
Reg Check constraint over a particular column [message #378573] Wed, 31 December 2008 00:24 Go to next message
ram anand
Messages: 244
Registered: February 2008
Location: india
Senior Member
Hi,
Is there is possible to check a particular column which has varchar2 as datatype not to insert the values with decimal point

for ex

if a column data is varchar2(20)

2.if we add the value as 1000.230

this value should not be inserted

I have tried it by check constraint but no use is there any possible way to do

thanks in advance
Re: Reg Check constraint over a particular column [message #378579 is a reply to message #378573] Wed, 31 December 2008 00:41 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Do you mean you want to use a varchar2 column to only allow integers? Or do you also allow other characters?
Re: Reg Check constraint over a particular column [message #378580 is a reply to message #378579] Wed, 31 December 2008 00:44 Go to previous messageGo to next message
ram anand
Messages: 244
Registered: February 2008
Location: india
Senior Member
No, both characters and integers but not integers with decimal point

thanks,
Re: Reg Check constraint over a particular column [message #378581 is a reply to message #378580] Wed, 31 December 2008 00:45 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8623
Registered: November 2002
Location: California, USA
Senior Member
You can do this with a check constraint. You can use INSTR to check for the presence of a decimal point.
Re: Reg Check constraint over a particular column [message #378582 is a reply to message #378581] Wed, 31 December 2008 00:46 Go to previous messageGo to next message
ram anand
Messages: 244
Registered: February 2008
Location: india
Senior Member
can u pls help on this

Thanks,
Re: Reg Check constraint over a particular column [message #378583 is a reply to message #378582] Wed, 31 December 2008 00:49 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Do you only want to prohibit the point/dot in case of a "numeric" value?
Re: Reg Check constraint over a particular column [message #378584 is a reply to message #378582] Wed, 31 December 2008 00:49 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8623
Registered: November 2002
Location: California, USA
Senior Member
You need to post what you have tried. You can find the syntax for INSTR in the online documentation, so that you can fill in the ... in the example below..


SCOTT@orcl_11g> CREATE TABLE test_tab
  2    (test_col  VARCHAR2 (20))
  3  /

Table created.

SCOTT@orcl_11g> ALTER TABLE test_tab
  2  ADD CONSTRAINT no_decimals
  3  CHECK (INSTR (...) = ...)
  4  /

Table altered.

SCOTT@orcl_11g> INSERT INTO test_tab VALUES ('1000.230')
  2  /
INSERT INTO test_tab VALUES ('1000.230')
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.NO_DECIMALS) violated


SCOTT@orcl_11g> 

Re: Reg Check constraint over a particular column [message #378587 is a reply to message #378584] Wed, 31 December 2008 00:53 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
However, another question is: do you want to restrict users from entering a dot in all cases; because, Barbara's solution will not allow you to enter "ram.anand" (as well as "1000.230").

[EDIT] Oooops! Frank has already asked that!

[Updated on: Wed, 31 December 2008 00:54]

Report message to a moderator

Re: Reg Check constraint over a particular column [message #378594 is a reply to message #378587] Wed, 31 December 2008 01:10 Go to previous messageGo to next message
ram anand
Messages: 244
Registered: February 2008
Location: india
Senior Member
No only for integers anyhow in our scenario no dots will be in characters ,

but the above said concept fails while giving this alter command

thanks,
Re: Reg Check constraint over a particular column [message #378596 is a reply to message #378594] Wed, 31 December 2008 01:13 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
ram anand wrote on Wed, 31 December 2008 08:10
No only for integers anyhow in our scenario no dots will be in characters ,

but the above said concept fails while giving this alter command

thanks,

What fails? I hope you didn't apply Barbara's "..." ALTER TABLE statement as it requires you to fill in the dots by meaningful code (which should be discovered by reading documentation about the INSTR function).
Re: Reg Check constraint over a particular column [message #378604 is a reply to message #378594] Wed, 31 December 2008 01:33 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
ram anand wrote on Wed, 31 December 2008 08:10
No only for integers anyhow in our scenario no dots will be in characters

Sounds like you are far from done.. I bet there are more characters that are not allowed if one enters an integer..
Solution? Use a number column for the integers and a varchar2 column for strings.
Previous Topic: Explanation needed for a Sql query
Next Topic: to compare with sys_refcursor what could be the possible values other than %NOTFOUND??
Goto Forum:
  


Current Time: Sat Dec 03 06:03:22 CST 2016

Total time taken to generate the page: 0.14152 seconds