constraint issues

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

constraint issues

Postby Mdjahangirpasha » Wed Dec 18, 2013 5:14 am

hi
would you resolve this issue?
I have created banking table (table name kcb_acc_tab) with columns [accno(account no.),name,acc_type(account type),balance]
I want to apply constraint on balance.
condition:
1)if account type is 's' then balance must be greater than or equal to 5000
2)if account type is 'c' then balance must be greater than or equal to 10000

I tried with check constraint as below but it's giving an error


" alter table kcb_acc_tab modify balance number(9,2) constraint chk_amt
check (if acc_type='S' then balance >=5000
else balance >=10000); "

Regards,
Mdjahangirpasha
Member
 
Posts: 22
Joined: Sun Dec 15, 2013 8:31 am

Re: constraint issues

Postby Tim... » Wed Dec 18, 2013 8:34 am

Hi.

You can't do this with a constraint. If that must be enforced, you need to do it with a trigger. If the trigger detects a problem, use RAISE_APPLICATION_ERROR to fail with a sensible message.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17936
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: constraint issues

Postby Mdjahangirpasha » Wed Dec 18, 2013 2:54 pm

Hi Tim
I want to apply check constraint on balance column such that it won't accept less than 5000 if the account type is 'S' and 10000 if account type is 10000

can't we apply constraint

Thanks :-)
Mdjahangirpasha
Member
 
Posts: 22
Joined: Sun Dec 15, 2013 8:31 am

Re: constraint issues

Postby Tim... » Wed Dec 18, 2013 5:07 pm

Hi.

No. Check constraints are really limited in the functionality they can include. You can't put IF statements in them. Read here:

http://docs.oracle.com/cd/B19306_01/ser ... sthref2913

If you rewrite it to a plain boolean expression though, it will work.

Code: Select all
CREATE TABLE kcb_acc_tab (
  acc_type VARCHAR2(1),
  balance  NUMBER
);

ALTER TABLE kcb_acc_tab ADD
   CONSTRAINT check_balance_ck
    CHECK ((acc_type='s' AND balance >= 5000) OR (acc_type='c' AND balance >= 10000) OR (acc_type NOT IN ('s','c'))
);

INSERT INTO kcb_acc_tab VALUES ('a', 1);
INSERT INTO kcb_acc_tab VALUES ('s', 5000);
INSERT INTO kcb_acc_tab VALUES ('s', 4999);
INSERT INTO kcb_acc_tab VALUES ('c', 10000);
INSERT INTO kcb_acc_tab VALUES ('c', 9999);


You have to understand the impact of such check constraints on the performance of a table. Any INSERT or UPDATE of these columns will trigger the check, with has a performance impact. I'm not saying you shouldn't do it. Just to be mindful.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17936
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: constraint issues

Postby Mdjahangirpasha » Thu Dec 19, 2013 3:08 pm

Hi
Tim...

Thanks a lot
it's my pleasure to have chat with
One more question can I
Mdjahangirpasha
Member
 
Posts: 22
Joined: Sun Dec 15, 2013 8:31 am

Re: constraint issues

Postby Tim... » Thu Dec 19, 2013 4:04 pm

Sure. Ask away.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17936
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: constraint issues

Postby Mdjahangirpasha » Fri Dec 20, 2013 5:11 am

Hi,
Is there any websites which provide in depth knowledge of oracle D2K, oracle forms and reports kindly share with me.

Thanks,
Regards,
Md Jahangir Pasha
Mdjahangirpasha
Member
 
Posts: 22
Joined: Sun Dec 15, 2013 8:31 am

Re: constraint issues

Postby Tim... » Fri Dec 20, 2013 9:00 am

Hi.

There really isn't much out there. The Designer product was end-of-lifed a long time ago, so there is little written about it these days. I would venture that most of the people stopped using it before blogging became popular. :)

In a similar vein, Oracle Forms and Reports is kind-of dying. There is a lot of it around because people have 20 years worth of applications they don't have time to redevelop, but nobody is really doing anything new with it these days, hence nobody is really writing about it.

If you can find some old books on Amazon, that would probably be your best source of information.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17936
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: constraint issues

Postby Mdjahangirpasha » Fri Dec 20, 2013 9:44 am

Hi
Tim
would you guide me for my career?
Mdjahangirpasha
Member
 
Posts: 22
Joined: Sun Dec 15, 2013 8:31 am

Re: constraint issues

Postby Tim... » Fri Dec 20, 2013 11:09 am

Hi.

I'm not sure I'm the right person for that job. There has been no planning in my career. I have just done what gets put in front of me for about 20 years. :)

If you have specific questions, I will try to answer them, but I have no master plan... :)

If I had one piece of advice, I would say, "Do what you enjoy!"

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17936
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: constraint issues

Postby Mdjahangirpasha » Tue Dec 24, 2013 6:49 am

Thanks
for your valuable suggestion
Mdjahangirpasha
Member
 
Posts: 22
Joined: Sun Dec 15, 2013 8:31 am

Re: constraint issues

Postby Tim... » Tue Dec 24, 2013 10:54 am

:)
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17936
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK


Return to Oracle SQL and PL/SQL Development

Who is online

Users browsing this forum: No registered users and 7 guests

cron