Check constraints for handling TOO MANY ROWS.

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

Check constraints for handling TOO MANY ROWS.

Postby Anand » Fri Aug 17, 2012 5:54 am

Hi Tim,

I need your assitance in the following requirement.

create table RC_COL_DETAILS
(
COL_ID NUMBER(5) not null,
COL_NAME VARCHAR2(30) not null,
DURATION_KEY NUMBER(1) default 0 not nul
);

-- ALLOWED , as col "DURATION_KEY" value is 0.
1. Insert into RC_COL_DETAILS values(1,'TOLL_VALUE',0);

-- ALLOWED only for single row, col "DURATION_KEY" with value 1.
2. Insert into RC_COL_DETAILS values(2,'BILL_DUR',1);

-- NOT ALLOWED , as only one row can have "DURATION_KEY" value as 1.
3. Insert into RC_COL_DETAILS values(3,'CALL_DUR',1);

How can I accompalish this using CHECK constraints? I mean how do I enforce constraints so that only one row can have the "DURATION_KEY" value as 1.
I don't want to write trigger for this.

Thanks & Regards,
Anand Kumar Ojha
Anand
Member
 
Posts: 22
Joined: Thu Jun 07, 2012 2:59 pm

Re: Check constraints for handling TOO MANY ROWS.

Postby Tim... » Tue Aug 21, 2012 9:01 am

Hi.

I thought I had already answered this question, but obviously not... :)

Question: Can there only ever be unique values in this column, or is it only in the case of "1" that it has to be unique?

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: 17937
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Check constraints for handling TOO MANY ROWS.

Postby Anand » Mon Aug 27, 2012 3:49 am

Hi Tim,

As per the requirement the column DURATION_KEY can have unique value
only in the case of "1", i.e. the following query should result only one row.

SELECT COL_NAME
FROM RC_COL_DETAILS INTO V_COL_NAME
WHERE DURATION_KEY = 1;

I mean only one entry should be there is RC_COL_DETAILS in the case where the value of col DURATION_KEY is "1" . But there could be multiple entries of "0" in RC_COL_DETAILS table.

Thanks,
Anand Ojha
Anand
Member
 
Posts: 22
Joined: Thu Jun 07, 2012 2:59 pm

Re: Check constraints for handling TOO MANY ROWS.

Postby Tim... » Mon Aug 27, 2012 7:10 am

Hi

In that case I would use an after statement trigger to check the data and raise an error if there were 2 two rows.

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: 17937
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