Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Location
    London
    Posts
    341

    Unanswered: CONSTRAINT CHECK (Username is only duplicated IF all other active flags are 'N')

    I have a table (TblAcm) that, due to an overnight communication has caused duplicate usernames to be input into the system. Duplicates on username are not unexpected, as they can still appear but on different conditions, i.e. they were supplied through different sites. I need to apply a constraint to the system but I cannot work out if this is even possible.

    I want to apply the constraint against the username so that duplicate usernames are allowed, but only if the active flag for the other 'dups' isn't "Y".

    EXAMPLE 1

    Row Username Site Active
    1 Christyxo 1203 Y
    2 Christyxo 1394 N

    If I attempt to add a new user called christyxo, the constraint should stop me because one existing record has an active = 'Y'


    EXAMPLE 2

    Row Username Site Active
    1 Christyxo 1203 N
    2 Christyxo 1394 N

    Now if I attempt to add a new user called christyxo, I am allowed to.

    Is this possible in any way?

    All I can begin to think of is;

    Code:
    ALTER TABLE TblAcm
    ADD CONSTRAINT UNQ_username (count(username) = count(active_flag = 'N'));

    Christy
    Last edited by christyxo; 03-20-15 at 14:27. Reason: mistype

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    A database trigger might do the job. Here's how:
    Code:
    SQL> create or replace trigger trg_bi_un
      2    before insert on tblacm
      3    for each row
      4  declare
      5    l_cnt number;
      6  begin
      7    select count(*)
      8      into l_cnt
      9      from tblacm
     10      where user_name = :new.user_name
     11        and active = 'Y';
     12
     13    if l_cnt > 0 then
     14       raise_application_error(-20001, 'Active username - insert not allowed');
     15    end if;
     16  end;
     17  /
    
    Trigger created.
    
    SQL> select * from tblacm;
    
            RN USER_NAME                  SITE A
    ---------- -------------------- ---------- -
             1 Christyxo                  1203 Y
             2 Christyxo                  1394 N
    
    SQL> insert into tblacm values (3, 'Christyxo', 2000, 'Y');
    insert into tblacm values (3, 'Christyxo', 2000, 'Y')
                *
    ERROR at line 1:
    ORA-20001: Active username - insert not allowed
    ORA-06512: at "SCOTT.TRG_BI_UN", line 11
    ORA-04088: error during execution of trigger 'SCOTT.TRG_BI_UN'
    
    
    SQL> update tblacm set active = 'N';
    
    2 rows updated.
    
    SQL> insert into tblacm values (3, 'Christyxo', 2000, 'Y');
    
    1 row created.
    
    SQL> insert into tblacm values (4, 'Littlefoot', 3000, 'Y');
    
    1 row created.
    
    SQL>

  3. #3
    Join Date
    Mar 2007
    Posts
    623
    @Littlefoot: As long as you use INSERT VALUES statement...

    @Christy: Constraint is a static rule restring a table content, so when defining it, you should re-state the requirements not to be based on dynamic actions like INSERT DML statement. Just reflect the table content.
    For example: in a table, there may be no duplicate values of USERNAME column in those rows which have ACTIVE column set to 'Y' (assuming that the newly inserted row has ACTIVE = 'Y'; if it had ACTIVE = 'N', should this check also be performed?).
    If my assumptions are correct, they may be assured by a simple check on uniqueness of USERNAME in rows where ACTIVE = 'Y' (note that two NULLs are not duplicate) using unique index:
    Code:
    create unique index unq_username
    on tblacm (case when active = 'Y' then username end)
    (see e.g. this example in the documentation for 11gR2: https://docs.oracle.com/cd/E11882_01...2.htm#BGEHDECJ)

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Quote Originally Posted by flyboy View Post
    @Littlefoot: As long as you use INSERT VALUES statement...
    Yes. Christyxo said "If I attempt to add a new user ..." so I translated "add" to "insert". That's why that trigger is a "before insert" one. If the requirement was different, the answer might have been different as well.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •