Oracle SQL检查存在约束 [英] Oracle SQL Check Exists Constraint

问题描述

我在Oracle SQL Developer（Train和Driver）中有两个简单的表，每个表都有一个公共属性（driverid）。基本上我想做的是不允许在驱动程序表中的特定驱动程序元组的任何更新，如果他们的driverid属性存在于火车表中。我试图添加以下约束，但它抛出一个'subquery不允许这里的错误。

I have two simple tables in Oracle SQL Developer (Train and Driver) each with a common attribute (driverid). Essentially what i want to do is to not allow any updates on a particular driver tuple in the driver table if their driverid attribute exists in the train table. I have tried to add the following constraint however it throws back an error of 'subquery not allowed here.'

alter table driver add constraint drivcheck CHECK 
 (NOT EXISTS(select driverid from train))

的挖掘和一般的感觉是，这个条件应该用触发器检查，所以我已经尝试创建一个触发器来做这项工作，但没有很多的成功。下面的触发器是我已经提出了。

I did a bit of digging around and the general feeling is that this condition should be checked with a trigger so I have tried to create a trigger to do the job but not having a lot of success. The below trigger is what i have come up with already.

create trigger drivcheck4
before update on driver
for each row
begin
declare
cursor dri is
select driverid from train where 'N' IN  
          (select availability 
           from driver 
           inner join train on    driver.driverid=train.driverid
           );
dri2 NUMBER;
begin
open dri;
loop
fetch dri into dri2;
exit when dri%NOTFOUND;
if check (exists (select * from dri2)) THEN
//Throw Error Section
else
//Allow update operation to take place
end if;
end loop;
close dri;
end;

我知道触发器可能执行的东西不同于之前描述的，但这只是我的实验结果。我的初始描述是我想实现的。如果有人有任何想法，我将非常感激。

I'm aware the trigger may be performing something different other than as described, but this is just the result of me experimenting. My initial description is what i am trying to achieve. If anyone has any thoughts, I would be very grateful!

推荐答案

Andrew，

您面对的是否有任何错误？沿着以下几行应该工作。如果您可以发布一些示例数据，也很容易测试。

Is there any error that you are facing? Something along the following lines should work. If you could post some sample data as well, it would be easy to test.

    create or replace trigger trg_bu_driver2
    before update on driver
    for each row
    declare
      l_cnt number;
    begin
    select count(*)
    into   l_cnt
    from   train
    where  driver_id = :new.driver_id;
    if (l_cnt > 0) then
       raise_application_error (-20001, 'new driver id exists in train table');
    end if;
    end trg_bu_driver2;
    /

测试：

    SQL> select * from driver;

     DRIVER_ID COLUMN2
    ---------- --------------------
            10 driver1

    SQL> select * from train;

     DRIVER_ID   TRAIN_ID COLUMN3
    ---------- ---------- --------------------
            20        100 train1

• 更新到列表中不存在的驱动程序ID

  • Updating to a driver id that does not exist in train table.

      1  update driver
      2  set    driver_id = 30
      3* where  driver_id = 10
    SQL> /
    
    1 row updated.
    
    No issues.
    
    SQL> rollback;
    
    Rollback complete.
    

  
  

  - 更新为存在的驱动程序ID在列表中，引发错误。

  --updating to a driver id that exists in train table, raises an error.

      SQL> update driver
        2  set    driver_id = 20
        3  where  driver_id = 10
        4  ;
      update driver
             *
      ERROR at line 1:
      ORA-20001: new driver id exists in train table
      ORA-06512: at "DMART_ETL.TRG_BU_DRIVER2", line 9
      ORA-04088: error during execution of trigger 'DMART_ETL.TRG_BU_DRIVER2'
  

  这篇关于Oracle SQL检查存在约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！