Home » SQL & PL/SQL » SQL & PL/SQL » CHECK constraint not working ?
icon9.gif  CHECK constraint not working ? [message #347096] Wed, 10 September 2008 12:27 Go to next message
vikas027
Messages: 11
Registered: September 2008
Junior Member
Hi all,

I am using Oracle SQL Developer Version 1.5.1
and
MySQL Server 5.0 as Database,

I am trying to enforce a check constraint in a simple table, but it is not working as it should be.

CREATE TABLE EMPSAL
(
ID INTEGER NOT NULL,
SALARY INTEGER CHECK (SALARY >= 15000)
);


Still I am able to insert values less than 15000. Confused
INSERT INTO EMPSAL values (57, 1000);


SELECT * FROM EMPSAL
ID SALARY -----------
27 1000
57 1000


Please help .

Thanks and Regards,
VIKAS
Re: CHECK constraint not working ? [message #347098 is a reply to message #347096] Wed, 10 September 2008 12:40 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Works for me. You must have a synonym or trigger running interference. Or, you did not show us what you did between your execution
FOO SCOTT>select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Solaris: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

FOO SCOTT>CREATE TABLE EMPSAL
  2  (
  3  ID INTEGER NOT NULL,
  4  SALARY INTEGER CHECK (SALARY >= 15000)
  5  );

Table created.

FOO SCOTT>INSERT INTO EMPSAL values (57, 1000);
INSERT INTO EMPSAL values (57, 1000)
*
ERROR at line 1:
ORA-02290: check constraint (FOO.SYS_C0076359) violated


FOO SCOTT>


[mod-edit] Heh, I didn't even see that it was MySQL.

[Updated on: Wed, 10 September 2008 15:04]

Report message to a moderator

Re: CHECK constraint not working ? [message #347099 is a reply to message #347096] Wed, 10 September 2008 12:42 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>MySQL Server 5.0 as Database,
So exactly why are you posting in an ORACLE forum?
Re: CHECK constraint not working ? [message #347100 is a reply to message #347099] Wed, 10 September 2008 12:51 Go to previous messageGo to next message
vikas027
Messages: 11
Registered: September 2008
Junior Member
anacedent wrote on Wed, 10 September 2008 23:12
>MySQL Server 5.0 as Database,
So exactly why are you posting in an ORACLE forum?



I am sorry, if I am in mistake.

I am using Oracle SQL Developer, so I thought this is the right forum.

It would be really helpful if you link me to the correct forum.
Re: CHECK constraint not working ? [message #347101 is a reply to message #347096] Wed, 10 September 2008 12:56 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
You already have the answer over in www.dbforums.com
The problem is with MYSQL database & not with Oracle Developer.
Re: CHECK constraint not working ? [message #347122 is a reply to message #347096] Wed, 10 September 2008 15:58 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
go to

http://www.dbforums.com/forumdisplay.php?f=5
Re: CHECK constraint not working ? [message #347161 is a reply to message #347101] Wed, 10 September 2008 22:38 Go to previous messageGo to next message
vikas027
Messages: 11
Registered: September 2008
Junior Member
anacedent wrote on Wed, 10 September 2008 23:26
You already have the answer over in www.dbforums.com
The problem is with MYSQL database & not with Oracle Developer.


Ya I got it now.
Thanks.
Re: CHECK constraint not working ? [message #347162 is a reply to message #347096] Wed, 10 September 2008 22:39 Go to previous messageGo to next message
beginoracle
Messages: 4
Registered: September 2008
Junior Member

Hi,


Does it pop up any error??

-If it does, may be your version have a different syntax or it may not be supporting.
-Drop the table and recreate the table again. and retry the whole things. It may probably work.
Re: CHECK constraint not working ? [message #347165 is a reply to message #347162] Wed, 10 September 2008 22:58 Go to previous messageGo to next message
vikas027
Messages: 11
Registered: September 2008
Junior Member
beginoracle wrote on Thu, 11 September 2008 09:09
Hi,


Does it pop up any error??

-If it does, may be your version have a different syntax or it may not be supporting.
-Drop the table and recreate the table again. and retry the whole things. It may probably work.


No, no error comes up.
I have tried recreating tables but that did not helped.
Re: CHECK constraint not working ? [message #347166 is a reply to message #347096] Wed, 10 September 2008 23:01 Go to previous message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
This thread is OFF TOPIC & is now locked.
Previous Topic: SQL*plus command
Next Topic: Analyze Table
Goto Forum:
  


Current Time: Sun Dec 04 12:50:04 CST 2016

Total time taken to generate the page: 0.07367 seconds