sql - Oracle 11g 第 2 版 : Can a sub-query be used within a create table check constraint?

Question

我正在尝试定义三个数据库表 - USERNAME、USER_SESSIONS 和 TOOLBOX_DIRS_REGISTERED。我可以毫无问题地定义前两个，但最后一个让我有点悲伤。理想情况下，我想将其定义为下面的代码块所示，但我认为子查询不能在检查约束内使用？

下面的代码块显示了我希望如何定义表 TOOLBOX_DIRS_REGISTERED。代码块的开头还包含一些注释，试图解释我对表定义使用的某些约束的想法。

-- Constraint : USERNAME_FK
-- ========================
--
-- USERNAME must contain a username which has been added to the table USERNAME.
--
-- Constraint : USER_SESSION_ID_FK
-- ===============================
--
-- USER_SESSION_ID must contain a user session ID which has been added to the table 
-- USER_SESSION.
--
-- Constraint : check_user_session_id
-- ==================================
--
-- In addition to the above constraint, USER_SESSION_ID must also belong to the 
-- username which is contained within USERNAME.

create table
TOOLBOX_DIRS_REGISTERED
(
 DIRNAME         varchar2(100) not null,
 USERNAME        varchar2(32)  not null,
 USER_SESSION_ID varchar2(32)  not null,
 AUTO_REGISTER   char          not null,
 constraint
   TOOLBOX_DIRS_REGISTERED_PK
   primary key (DIRNAME),
 constraint
   USERNAME_FK
   foreign key (USERNAME)
   references USERS(USERNAME),
 constraint
   USER_SESSION_ID_FK
   foreign key (USER_SESSION_ID)
   references USER_SESSIONS(USER_SESSION_ID),
 constraint
   check_user_session_id
   check
   (
    USERNAME in
    (
     select USERNAME from USER_SESSIONS
     where USER_SESSIONS(USER_SESSION_ID) = USER_SESSION_ID
    )
   )
);

有谁知道解决这个问题的方法，即在检查约束“check_user_session_id”的定义中使用子查询？我在 Stackoverflow 上读到，在这种特殊情况下可以使用物化 View 代替子查询。问题是，如果我使用物化 View ，那么我想确保它在执行检查约束时是最新的。所以我所做的就是实现物化 View 以及在物化 View 上调用 dbms_mview.refresh 的触发器。这一切都很好，只是 Oracle 提示它无法在触发器中 COMMIT。呃!它给我的确切信息是；

ERROR at line 2:
ORA-04092: cannot COMMIT in a trigger
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2760
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2740
ORA-06512: at "SYSTEM.SIMULAB_MVIEW", line 19
ORA-06512: at "SYSTEM.TRIG_TOOLBOX_DIRS_REGISTERED", line 3
ORA-04088: error during execution of trigger 'SYSTEM.TRIG_TOOLBOX_DIRS_REGISTERED'

我假设 Oracle 在刷新物化 View 后尝试自动执行提交，这就是它所提示的？

我的触发器定义如下；

create or replace trigger TRIG_TOOLBOX_DIRS_REGISTERED
  before insert or update on
    TOOLBOX_DIRS_REGISTERED
  begin

    -- Invoke the PL/SQL Package procedure simulab_mview.refresh_mview

    simulab_mview.refresh_mview;

  end;

而 PL/SQL 包 simulab_mview 定义如下；

create or replace
package
simulab_mview
as

    procedure
    refresh_mview;

end;
/

create or replace package body
simulab_mview
as

    procedure
    refresh_mview
    as

    begin

        -- I have a strong suspicion that dbms_mview.refresh might cause a commit to 
        -- be executed. This would make sense, as the RDBMS would need to execute a 
        -- commit so that other clients could see the result of the refresh.

        dbms_mview.refresh('mat_view', 'C');

    end;

end;
/

有人对此有什么想法吗？有没有更简单的方法来做我想做的事情，我应该不使用物化 View ，不应该使用触发器吗？

对此的任何帮助甚至想法都将不胜感激。如果有人确实能够就此事提供任何帮助或想法，那么我要提前感谢他们的帮助。

祝你有美好的一天。

Answer 1

根据 Oracle 11.2 docs on CHECK constraints

Restrictions on Check Constraints

Check constraints are subject to the following restrictions:

• Conditions of check constraints cannot contain the following constructs:

  ◦Subqueries and scalar subquery expressions

如果您想验证 TOOLBOX_DIRS_REGISTERED 和 USER_SESSIONS 之间的 USER_SESSION_ID 和 USERNAME，我建议您在 USER_SESSIONS(USER_SESSION_ID, USERNAME) 上创建一个 UNIQUE 键，然后在 TOOLBOX_DIRS_REGISTERED(USER_SESSION_ID, USERNAME) 上创建一个外键到 USER_SESSIONS(USER_SESSION_ID) ，用户名)。

分享并享受。