0

I am having a table in oracle without a primary key and table is having 5 columns.

Every time while making the insert, i have to ensure that the same combination of records are not bing inserted.

For every row if a specific value has been inserted then a common value like 'ALL' should not be allowed to be inserted.


E.G: Existing Record in Table
INSERT INTO tbl ( PRODUCT_NAME, REQUEST_TYPE, ENVIRONMENT, PROBLEM_LEVEL, ) VALUES ( 'Intellect', 'BAU Enhancement', 'DEV', 'Critical')

If the above record is available then the below two record needs to be restricted but the last one can be allowed on account of its uniqueness.


1. INSERT INTO tbl ( PRODUCT_NAME, REQUEST_TYPE, ENVIRONMENT, PROBLEM_LEVEL) VALUES ( 'Intellect', 'BAU Enhancement', 'DEV', 'Critical')

And

2 . INSERT INTO tbl ( PRODUCT_NAME, REQUEST_TYPE, ENVIRONMENT, PROBLEM_LEVEL) VALUES ( 'ALL', 'BAU Enhancement', 'DEV', 'Critical')

But Below can be allowed:

3. INSERT INTO tbl ( PRODUCT_NAME, REQUEST_TYPE, ENVIRONMENT, PROBLEM_LEVEL) VALUES ( 'ALL', 'BAU Enhancement', 'DEV', 'Serious')

Request your help on this.

2
Contributors
4
Replies
5
Views
6 Years
Discussion Span
Last Post by p4uk80
0

Create a unique index on the table, specifying your; REQUEST_TYPE, ENVIRONMENT, PROBLEM_LEVEL columns.

0

Thnaks for your reply. But i have to accomodate the uniqueness considering 'ALL' value as well. For example, if in country column, 'ALL' has been inserted then for a new row, the same set of column values along with 'India' can't be inserted because 'India' is a part of 'ALL' country.

0

Well... If you are going to change your criteria... You're on your own.
(Your original post said that you were NOT to include 'ALL'!)

0

I am telling that for every row if a specific value has been inserted then a common value like 'ALL' should not be allowed to be inserted.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.