Forum Stats

  • 3,839,096 Users
  • 2,262,451 Discussions
  • 7,900,858 Comments

Discussions

Help enforcing constraints

713267
713267 Member Posts: 20
edited Oct 2, 2009 10:56AM in SQL & PL/SQL
I have the following challenge in front of me:

create table company
(
companyid number(10),
code varchar2(50),
descr varchar2(100),
companylevel number(10),
parentcode varchar2(50)
)

Example of data:

insert into company values (1, 'A', 'A parent', 10, null);
insert into company values (2, 'B', 'B parent', 10, null);
insert into company values (3, 'C', 'C child', 0, 'A');
insert into company values (4, 'D', 'D child', 0, 'B');
insert into company values (5, 'E', 'E parent', 10, null);

companylevel is usually 0 or 10
0 is child company
10 is a parent company

Rules that I need to apply:*

1) code and parentcode should be unique
*2) We should not allow an insert of a code with companylevel of 0 if that code already exists as a companylevel 10*
*3) We should not allow an insert of a code with companylevel of 10 if that code already exists as a companylevel 0*

As example, we should NOT allow the following inserts:

insert into company values (6, 'B', 'B child', 0, 'A');
insert into company values (7, 'C', 'C parent', 10, null);

But we should allow:

insert into company values (8, 'C', 'C child', 0, 'B');
insert into company values (9, 'D', 'D child', 0, 'A');

I need to work with what I have. I cannot modify the table.

I have tried to create a trigger but was not very successful at it. I get the famous MUTATING problem so I was wondering if I can create a constraint for Rules 2 and 3.
I am open for any suggestions.

Thanks a bunch!
Tagged:
«1

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,246 Red Diamond
    edited Oct 2, 2009 8:52AM
    Hi,

    I think you need two constraints or indexes.
    (1) A UNIQUE constraint on (code , parentcode)
    (2) a unique function-based index will do that.
    CREATE UNIQUE INDEX code_companylevel
    ON company
    ( CASE
          WHEN  companylevel IN (0, 10)
          THEN  code
      END
    );
    will only allow one row per company with either companylevel 0 or 10.
    Any number of rows can have the same code and any other companylevel.

    Edited by: Frank Kulash on Oct 2, 2009 8:48 AM
    Frank Kulash
  • 6363
    6363 Member Posts: 6,642
    edited Oct 2, 2009 8:52AM
    Thanks you for the create table and insert data scripts.

    You can use a unique function based index.
    SQL> create table company
      2  (
      3  companyid number(10),
      4  code varchar2(50),
      5  descr varchar2(100),
      6  companylevel number(10),
      7  parentcode varchar2(50)
      8  );
    
    Table created.
    
    SQL> insert into company values (1, 'A', 'A parent', 10, null);
    
    1 row created.
    
    SQL> insert into company values (2, 'B', 'B parent', 10, null);
    
    1 row created.
    
    SQL> insert into company values (3, 'C', 'C child', 0, 'A');
    
    1 row created.
    
    SQL> insert into company values (4, 'D', 'D child', 0, 'B');
    
    1 row created.
    
    SQL> insert into company values (5, 'E', 'E parent', 10, null);
    
    1 row created.
    
    SQL> commit;
    
    Commit complete.
    
    SQL> create unique index company_idx on company (case when companylevel in (0, 10) then code end);
    
    Index created.
    
    SQL> insert into company values (6, 'E', 'E parent', 1, null);
    
    1 row created.
    
    SQL> insert into company values (6, 'E', 'E parent', 0, null);
    insert into company values (6, 'E', 'E parent', 0, null)
    *
    ERROR at line 1:
    ORA-00001: unique constraint (TEST.COMPANY_IDX) violated
    
    
    SQL> insert into company values (6, 'E', 'E parent', 10, null);
    insert into company values (6, 'E', 'E parent', 10, null)
    *
    ERROR at line 1:
    ORA-00001: unique constraint (TEST.COMPANY_IDX) violated
    
    
    SQL> insert into company values (6, 'E', 'E parent', 2, null);
    
    1 row created.
    
    SQL>
    Edited by: Pointless on Oct 2, 2009 8:52 AM

    Beaten to it by Frank, but at least you get an example also
    6363
  • Hoek
    Hoek Member Posts: 16,087 Gold Crown
    Was thinking about the same.
    However:
    MHO%xe> CREATE UNIQUE INDEX code_companylevel
      2  ON company
      3  ( CASE
      4        WHEN  companylevel IN (0, 10)
      5        THEN  code
      6    END
      7  );
    
    Index is aangemaakt.
    
    Verstreken: 00:00:09.43
    MHO%xe> insert into company values (6, 'B', 'B child', 0, 'A');
    insert into company values (6, 'B', 'B child', 0, 'A')
    *
    FOUT in regel 1:
    .ORA-00001: unique constraint (MHO.CODE_COMPANYLEVEL) violated
    
    
    Verstreken: 00:00:01.50
    MHO%xe> insert into company values (7, 'C', 'C parent', 10, null);
    insert into company values (7, 'C', 'C parent', 10, null)
    *
    FOUT in regel 1:
    .ORA-00001: unique constraint (MHO.CODE_COMPANYLEVEL) violated
    
    
    Verstreken: 00:00:00.09
    MHO%xe> insert into company values (8, 'C', 'C child', 0, 'B');
    insert into company values (8, 'C', 'C child', 0, 'B')
    *
    FOUT in regel 1:
    .ORA-00001: unique constraint (MHO.CODE_COMPANYLEVEL) violated
    
    
    Verstreken: 00:00:00.07
    MHO%xe> insert into company values (9, 'D', 'D child', 0, 'A');
    insert into company values (9, 'D', 'D child', 0, 'A')
    *
    FOUT in regel 1:
    .ORA-00001: unique constraint (MHO.CODE_COMPANYLEVEL) violated
    8 and 9 get discarded, but should be allowed...
    Hoek
  • 713267
    713267 Member Posts: 20
    hoek, that's exactly my problem. I have tried to create that Unique index to find out it doesn't work with 8 or 9.
    I will not give up. There has to be a way...
  • Hoek
    Hoek Member Posts: 16,087 Gold Crown
    There has to be a way...
    There are several ways, but I assume you try to avoid the path of serializing access to your table here...
  • 713267
    713267 Member Posts: 20
    I have not come across that solution. Can you ellaborate?
  • Hoek
    Hoek Member Posts: 16,087 Gold Crown
    Some slight similar examples and the available options can be found here:
    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:7144179386439
    (Or do a search on 'unique constraint' on Tom's homepage.)

    So, perhaps you could add some function that makes the nessecary checks.
    However, I haven't got it fully working yet, since I'm doing this 'in between many other things'.
  • 713267
    713267 Member Posts: 20
    BTW, I failed to mention that the table has another column called DATACHECK NUMBER(10)
    The current solution (which doesn't work properly) has the following constraint on that column:

    alter table company add CHECK (DATACHECK <> 1);

    This was their attempt to use a trigger to do the validation.
  • 713267
    713267 Member Posts: 20
    sorry the check if for DATACHECK different than 1
    Having problems with the HTML here
  • Centinul
    Centinul Member Posts: 6,871 Bronze Crown
    edited Oct 2, 2009 9:45AM
    Where is rule #4 that defines the logic to allow rows 8 and 9 to be inserted?
This discussion has been closed.