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.

Member Avatar for hfx642

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

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.

Member Avatar for hfx642

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'!)

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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.