使用其他表中的子查询添加约束 [英] Adding constraints using subquery from other table

问题描述

我有2张桌子:

1. MESSAGES ( ID(pk), SENDER )

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

这是交易:

1. 一条消息只能有一个发件人. 完成

一条消息可以有多个接收者，但是一个接收者不能多次接收同一条消息. 完成

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

发件人无法向自己发送消息. 我该如何做?

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

我尝试过:

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

在Oracle数据库10g XE上，但是我收到以下错误:

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

此处不允许子查询

SUB QUERY NOT ALLOWED HERE

推荐答案

您可以采取的一种解决方法是创建一个物化视图，其中包含一个用于标识不良行"的查询.

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;

因此查询应始终返回0行，对吗? 物化视图的作用是在任何人对表messages或receivers进行DML操作时刷新自身.因此，从理论上讲，如果有人向自己插入一条消息，则查询将返回bad_rows = 1.但是，我还对物化视图添加了一个约束条件，说bad_rows列的唯一允许值为0.Oracle不允许您提交任何提供另一个值的事务.

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.

因此，如果查看第二对插入语句，您会看到我已经设法在接收器中插入了错误的行，但是当我尝试提交时，Oracle违反了约束.

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.

这篇关于使用其他表中的子查询添加约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！