1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

To restrict duplicate row in an oracle table

Discussion in 'SQL PL/SQL' started by p4uk80, Nov 10, 2011.

  1. p4uk80

    p4uk80 Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Hi,

    I am having a table in oracle without a primary kay 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 comman value like 'ALL' should not be allowed to be inserted and vice versa.

    E.g:
    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.

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

    And

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

    Request your help on this.
     
  2. robin

    robin Forum Advisor

    Messages:
    55
    Likes Received:
    7
    Trophy Points:
    160
    Location:
    Hyderabad
    Let us say u have a table emp(id number,name varchar2(12))

    Now,If id is already present then new insertions should not be possible.Furthermore,name should cannot be 'all'


    We define a trigger here,

    create or replace trigger emp_insert
    before insert on emp
    for each row
    declare eid number;
    begin
    select count(*) into eid from emp where id=:new.id;
    if eid <> 0 or :new.name='all' then
    raise_application_error(-20293,'cannot insert');
    end if;
    end;

    Now whenever u try to insert a record with the same id or name with 'all' then raise_application_error will be fired.

    Hope this helps...
     
    Sadik likes this.
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Which will work just fine if only one session is actively attempting an insert. The select is dependent upon committed values, not pending values, and two or more sessions attempting to insert the same value can succeed as the trigger won't 'see' the uncommitted inserts.

    The only way to prevent duplicates in a table is through a primary key.
     
  4. p4uk80

    p4uk80 Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Hello Dear,

    I can not define a primary key on the table as it won't allow for insertion of a row of different combination.

    I can use the below query but once 'ALL' is inserted for one column in a row it is not allowing another row to be inserted with specific value.

    SELECT COUNT(*) FROM TBL1
    WHERE(( UPPER(PRODUCT_NAME)=UPPER('solve')
    AND UPPER(PROBLEM_LEVEL)=UPPER('Critical')
    AND UPPER(REQUEST_TYPE)=UPPER('ALL')
    AND UPPER(ENVIRONMENT)=UPPER('Production')
    AND NVL(UPPER(COUNTRY),'ALL')=UPPER('USA'))
    OR (UPPER(PRODUCT_NAME)<>UPPER('solve') AND (UPPER(PRODUCT_NAME)='ALL' OR UPPER('solve')='ALL'))
    OR (UPPER(PROBLEM_LEVEL)<>UPPER('Critical') AND (UPPER(PROBLEM_LEVEL)='ALL' OR UPPER('Critical')='ALL'))
    OR (UPPER(REQUEST_TYPE)<>UPPER('ALL') AND (UPPER(REQUEST_TYPE)='ALL' OR UPPER('ALL')='ALL'))
    OR (UPPER(ENVIRONMENT)<>UPPER('Production') AND (UPPER(ENVIRONMENT)='ALL' OR UPPER('Production')='ALL'))
    OR (NVL(UPPER(COUNTRY),'ALL')<>UPPER('USA') AND (NVL(UPPER(COUNTRY),'ALL')='ALL' OR UPPER('USA')='ALL')))
    AND eff_to IS NULL
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Then you redefine your primary key to include more than one column, so the combination of values is unique.
     
  6. p4uk80

    p4uk80 Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Thanks 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.

    It will not allow if primary key is defined.
     
  7. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Not according to Oracle as 'ALL' and 'India' are two distinct character values. The PK will allow such entries, however your business rules may not. Please provide an example illustrating what you want so all can see and understand your problem.
     
  8. p4uk80

    p4uk80 Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Dear members,

    Please see below example:

    Suppose table is having below record

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

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

    As this is the same set of records

    And

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


    As the Product_name as 'ALL' with same set of records can't be inserted because the same record is available as per point 1.

    Basically Point 3 is a subset of Point 1 hence needs to be restricted.
     
  9. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    You might need to combine

    1. Primary key combination for the relevant columns and
    2. Trigger to restrict the values in case 'ALL' present