1

For simplicity (and since it captures the essence of my problem), suppose I have a table with data.

CREATE TABLE my_practice_table (

my_id       NUMBER(12), 

field_2     VARCHAR2(20),

field_3     VARCHAR2(20)

);

I want to define a new constraint on the table that has the following logic:

For any new record x inserted into my_practice_table,

if (x.field_2 is null)

   if [there exists a record y in the table such that 

   i.) y.my_id == x.my_id 

   AND

   ii.) y.field_2 is not null
    ]

       --do not insert x into the table

   else
      --there exists no such y in the table, so we insert x

else (x.field_2 is not null)

 if [there exists a record y in the table such that
    i.) y.my_id == x.my_id
    AND
    ii.) y.field_2 is null
   ]
     --do not insert x into the table
else 
     --there exists no such y in the table, so we insert x

Example: Suppose my_practice_table had the following data table1: [1 'hi' 'bye' ] [2 'hello' 'adios'] [1 'hi' 'seeya']

The statement: INSERT INTO table1 (my_id, field_2, field_3) VALUES (1, null, 'ciao');

won't work because there is already a record in table1 with my_id equal to 1 and field_2 not equal to null.

Example: Suppose my_practice_table had the following data: table2: [1 'hi' 'bye' ] [2 null 'adios'] [1 'hi' 'seeya']

The statement: INSERT INTO table2 (my_id, field_2, field_3) VALUES (2, 'hola', 'this won't work');

won't work because there is already a record in table2 with my_id equal to 2 and field_2 equal to null.

My question is what is the way to go about this in Oracle syntax. The algorithm is simple enough, but I'm just not comfortable enough with Oracle syntax to get the job done. Do I use a BEFORE INSERT Trigger? How do I scan for values in other fields of a particular row once I found a match on the field my_id? Is there a 'break' statement in Oracle that I can do to speed this up? Any help or guidance to the right place to look for a possible solution would be appreciated. Thanks

1

If you query the table you're inserting into inside a trigger on that same table, you are likely to get "ORA-04091: table name is mutating, trigger/function may not see it." error. Check constraints cannot contain subqueries. Therefore your best option is to encapsulate logic for inserting data into a stored procedure and use that procedure instead of direct inserts.

I can assist you with writing the procedure, however, it seems to me that you have a logical flaw in your data model. Your example data shows that my_id is not a unique key for the table but rather that many rows may share the same value for my_id. So if one row with my_id = 1 has field_2 set to some value and for some other row field_2 is null, what is to be done? Don't you have a primary key in your table?

Anyways, if you want literally what you wrote in your post, you could do with something like

create or replace procedure my_practice_table_insert(p_my_id in number, p_field_2 in varchar2, p_field_3 in varchar2)
is
    l_cnt number;
begin
    if p_field_2 is null then
        select count(*) into l_cnt from my_practice_table where my_id = p_my_id and field_2 is not null;
    else
        select count(*) into l_cnt from my_practice_table where my_id = p_my_id and field_2 is null;
    end if;

    if l_cnt = 0 then
        insert into my_practice_table(my_id, field_2, field_3) values(p_my_id, p_field_2, p_field_3);
    end if;
end;

I didn't actually try out the procedure so it may have slight errors, but that's the general form it could take.

  • thanks for the procedure. This would be helpful, but the issue is that this table will be queried not only from Oracle, but from Java, so going through all the places where an insert may be happening and implementing logic would ideally be avoided if a single change at the table could be made. And about the primary key.. my_id is not referring to a primary key. I should've made that explicit. It's a key to another table, but that I felt was irrelevant. Thanks again. – Hunter Alzate Apr 21 '15 at 1:06
  • I understand why a trigger seems right for you but I still think the procedure is a better approach. You can find many arguments why you shouldn't use a trigger unless its absolutely necessary, for example link. If you still insist on using a trigger, you could rename your table, create a view which has the same name as the table had before, and create an "instead of insert" trigger on that view, where you would perform the necessary checks. – zgguy Apr 21 '15 at 7:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.