Hi Guys,
I have a question. I created a table with columns such as 'TRANSACTION_STATUS_ID', 'REASON_CODE', 'EFFECTIVE_TO', and more.
Here, I want to create a unique constraint or unique index on a combination of three columns: 'TRANSACTION_STATUS_ID', 'REASON_CODE', and 'EFFECTIVE_END_DATE'. My requirement is to disallow duplicate records in the table for these three columns.
ALTER TABLE spi_transaction_prc_reasons
ADD constraint transaction_prc_reasons_unique unique(transaction_status_id, reason_code, CASE
WHEN effective_end_date IS NULL THEN
effective_to
END);
or
CREATE UNIQUE INDEX transaction_prc_unique_index ON spi_transaction_prc_reasons (transaction_status_id, reason_code, effective_to)
WHERE effective_to IS NULL;
CREATE UNIQUE INDEX transaction_prc_unique_index ON spi_transaction_prc_reasons
(transaction_status_id,reason_code, DECODE(effective_to,NULL,effective_to);
Combination is : transaction_status_id, reason_code and effective_end_date is null (effective_end_date is acive records)
If a new record with the same data is received, I do not want to insert this record. Instead, I want to receive a constraint violation error.
Pls, help me with this issue.