Check constraints for handling TOO MANY ROWS.

[Anand]

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

[Tim...]

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...

[Anand]

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

[Tim...]

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...