1

I have 2 tables:

  1. MESSAGES ( ID(pk), SENDER )

  2. RECIEVERS ( ID references MESSAGE(ID), RECIEVER, pk(ID, RECIEVER) )

Here's the deal:

  1. A message can have only one sender. DONE

  2. A message can have multiple receivers, but a receiver cannot receive the same message more than once. DONE

  3. A sender cannot send a message to himself. How do I do this part?

I tried this:

update table RECIEVERS add constraint "RECIEVERS_CK_SELF_SEND"
( RECIEVER not in
     ( select SENDER
        from MESSAGES
         where MESSAGE.ID=RECIEVER.ID));

on Oracle Database 10g XE, but I receive the following error:

SUB QUERY NOT ALLOWED HERE

NoobEditor
  • 15,563
  • 19
  • 81
  • 112
Mayank Sharma
  • 1
  • 6
  • 1
    see http://stackoverflow.com/questions/16045505/can-a-check-constraint-relate-to-another-table-oracle – Ian Kenney Mar 06 '14 at 07:21
  • 1
    Note that, in English, the word is spelled rec**ei**vers (the rule-of-thumb "*'**i**' before '**e**' except after '**c**'*" was drummed into me as a child). – eggyal Mar 06 '14 at 07:29
  • @eggyal - of course the 'rule' has more exceptions than rule - fact! – Strawberry Mar 06 '14 at 07:40
  • That's what before insert triggers are for (though i personally think triggers are good for nothing.... i would validate in the application). – haki Mar 06 '14 at 09:05
  • Triggers will not solve a problem because they do not see other transactions – smnbbrv Mar 06 '14 at 09:30

1 Answers1

0

One work-around you can do is to create a materialized view containing a query identifying the "bad rows".

create table messages(
   message_id  number       not null
  ,sender_id   varchar2(20) not null
  ,primary key(message_id)
);

create table receivers(
   message_id  number       not null
  ,receiver_id varchar2(20) not null
  ,primary key(message_id,receiver_id)
  ,foreign key(message_id) references messages(message_id)
);

create materialized view log 
    on receivers with primary key, rowid including new values;

create materialized view log 
    on messages  with primary key, rowid (sender_id) including new values;

create materialized view mv 
refresh fast on commit
as
select count(*) as bad_rows 
  from messages  m
  join receivers r using(message_id)
 where m.sender_id = r.receiver_id;

alter materialized view mv
  add constraint dont_send_to_self check(bad_rows = 0);

Now let's try to insert some rows:

SQL> insert into messages(message_id, sender_id)    values(1, 'Ronnie');
1 row created.

SQL> insert into receivers(message_id, receiver_id) values(1, 'Mayank Sharma');
1 row created.

SQL> commit;
Commit complete.

That went well. Now let's send a message to myself:

SQL> insert into messages(message_id, sender_id) values(2, 'Ronnie');    
1 row created.

SQL> insert into receivers(message_id, receiver_id) values(2, 'Ronnie');    
1 row created.

SQL> commit;
commit
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-02290: check constraint (RNBN.DONT_SEND_TO_SELF) violated

Edit, more explanation: Ok, this query (in the materialized view definition), identifies and counts all the messages that are being sent to oneself. That is, all the rows that violate the rule you stated. 

select count(*) as bad_rows 
  from messages  m
  join receivers r using(message_id)
 where m.sender_id = r.receiver_id;

So the query should return 0 rows at all times, right? What the materialized view does, is to refresh itself when anyone commits a DML operation against the tables messages or receivers. So in theory, if someone inserts a message to herself, the query would return bad_rows = 1. But, I've also included a constraint on the materialized view, saying that the only allowed value for column bad_rows is 0. Oracle will not let you commit any transaction that gives another value.

So if you look at the second pair of insert statements, you can see that I've managed to insert the erroneous row in receivers, but Oracle gives a constraint violation when I try to commit.

Ronnis
  • 12,593
  • 2
  • 32
  • 52