sql - Adding constraints using subquery from other table -

i have 2 tables:

1. messages ( id(pk), sender )

2. recievers ( id references message(id), reciever, pk(id, reciever) )

here's deal:

1. a message can have 1 sender. done

2. a message can have multiple receivers, receiver cannot receive same message more once. done

3. a sender cannot send message himself. how do part?

i tried this:

update table recievers add constraint "recievers_ck_self_send" ( reciever not in      ( select sender         messages          message.id=reciever.id)); 

on oracle database 10g xe, receive following error:

sub query not allowed here

one work-around can create materialized view containing query identifying "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 primary key, rowid including new values;  create materialized view log      on messages  primary key, rowid (sender_id) including new values;  create materialized view mv  refresh fast on commit select count(*) bad_rows    messages  m   join receivers r using(message_id)  m.sender_id = r.receiver_id;  alter materialized view mv   add constraint dont_send_to_self check(bad_rows = 0); 

now let's try insert rows:

sql> insert messages(message_id, sender_id)    values(1, 'ronnie'); 1 row created.  sql> insert receivers(message_id, receiver_id) values(1, 'mayank sharma'); 1 row created.  sql> commit; commit complete. 

that went well. let's send message myself:

sql> insert messages(message_id, sender_id) values(2, 'ronnie');     1 row created.  sql> insert receivers(message_id, receiver_id) values(2, 'ronnie');     1 row created.  sql> commit; commit * error @ line 1: ora-12008: error in materialized view refresh path ora-02290: check constraint (rnbn.dont_send_to_self) violated 

edit, more explanation: ok, query (in materialized view definition), identifies , counts messages being sent oneself. is, rows violate rule stated.

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

so query should return 0 rows @ times, right? materialized view does, refresh when commits dml operation against tables messages or receivers. in theory, if inserts message herself, query return bad_rows = 1. but, i've included constraint on materialized view, saying allowed value column bad_rows 0. oracle not let commit transaction gives value.

so if @ second pair of insert statements, can see i've managed insert erroneous row in receivers, oracle gives constraint violation when try commit.