检查约束调用函数Oracle SQL开发人员 [英] Check Constraint Calling a Function Oracle SQL developer

问题描述

在Oracle SQL开发人员中是否可以做这样的事情

Is it possible in Oracle SQL developer to do something like this

CREATE FUNCTION fnCheckValid(accountidd IN NUMBER) 
RETURN NUMBER 
   IS retval NUMBER(4,0);
   BEGIN 
      SELECT COUNT(accountid_fk) 
      INTO retval 
      FROM tbl_AccountAuthentications 
      WHERE accountid_fk = accountidd; 
      RETURN(retval); 
    END;
/


ALTER TABLE tbl_AccountAuthentications
ADD CONSTRAINT chkCheckvalid CHECK(fnCheckValid(accountid_fk) <= 1);

我一直遇到的错误是

Error starting at line 999 in command:
ALTER TABLE tbl_AccountAuthentications
ADD CONSTRAINT chkCheckvalid CHECK(fnCheckValid(accountid_fk) <= 1)
Error report:
SQL Error: ORA-00904: "FNCHECKVALID": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:

该函数正在创建中，我可以找到它，但是当我尝试调用它时，我不断收到该错误

The function is being created and i can find it but when i am trying to call it i keep getting that error

这就是我想要实现的目标

this is what i am trying to achieve

AccountID    RegularID     OpenID
 1            5             null
 1            null          10
 1            null          11
 1            6                             <-- Forbidden

，以便用户不能创建2个普通帐户，但可以拥有任意数量的OpenID帐户

so that a user cannot make 2 regular accounts but can have as many OpenID accounts as he wants

表设置如下

CREATE TABLE tbl_AccountAuthentications(
        newAuthID NUMBER(4,0)
          CONSTRAINT naid_pk PRIMARY KEY,        
        accountid_fk NUMBER(4,0)
          CONSTRAINT accid_fk 
            REFERENCES tbl_UserAccounts(account_id),
        regularid_fk NUMBER(4,0)
          CONSTRAINT rgid_fk
            REFERENCES tbl_StrongRoom(password_id),
        openid_fk NUMBER(4,0)
          CONSTRAINT opid_fk
            REFERENCES tbl_OpenID(openid)
);

推荐答案

无论您如何确保列 AccountID 和 RegularID 都是唯一的 OpenID 的值很多.

You want to ensure that the columns AccountID and RegularID, together, are unique, no matter how many values of OpenID there are.

您已经确定，执行此操作的唯一方法是约束.您会在注释中注意到您已尝试使用触发器.这不会不限制数据库中的值，仅确保它尝试验证何时启用了触发器.我希望当您尝试进行操作时，收到错误"ORA-04091:正在变异，触发器/函数可能看不到它." ，因为您要从表中选择更改(插入或更新)过程.

The only way of doing this, as you've determined, is to constrain it. You note in the comments that you've experimented with triggers. This does not constrain the values within the database, it only ensures that it attempts to verify when the trigger is enabled. I expect that, when you attempted it, you got the error "ORA-04091: is mutating, trigger/function may not see it." as you're selecting from a table you're in the processes of changing (insert or update).

如果您必须对此加以限制，那么这就是您应该做的；这样做的问题是您的表未规范化.因此，将其标准化.使用两个表，而不要使用一个表.

If you have to constrain this then that's what you should do; the problem with doing so is that your table is not normalised. So, normalise it. Use two tables instead of the one you have.

首先，您需要您的 RegularID 在 AccountID 上是唯一的，这意味着它应该存储在此级别.似乎tbl_UserAccounts在此标识符上是唯一的，因此请更改此表并在其中存储您的 RegularID .

Firstly, you need your RegularID to be unique over AccountID this means it should be stored at this level. It appears as though tbl_UserAccounts is unique on this identifier so alter this table and store your RegularID there.

接下来，您想要一个表具有与用户可能想要的一样多的 OpenID .这意味着您需要一个在 AccountID 和 OpenID ¹ 上唯一的表.

Next, you want a table that has as many OpenID s as a user might want. This means that you need a table unique on AccountID and OpenID ¹.

create table openid_account_auth (
   , account_id number(4,0)
   , open_id number(4,0)
   , constraint pk_openid_account_auth
       primary key (account_id, open_id)
   , constraint fk_openid_account_auth_accid
       foreign key (account_id)
       references tbl_UserAccounts(account_id)
   , constraint fk_openid_account_auth_open
       foreign key (open_id)
       references tbl_OpenID (openid)
     );

此表(以及您自己的表)上的一点，表示多个帐户可以具有相同的 OpenID .如果您不打算这样做，则应在tbl_OpenID中添加 AccountID 作为外键，这是确保每个 OpenID 与之关联的唯一方法，只有一个 AccountID .

One point on this table (and your own), it means that multiple accounts can have the same OpenID. If you did not intend this then you should add AccountID as a foreign key in tbl_OpenID, which would be the only way to ensure that each OpenID is associated with one, and only one, AccountID.

然后，如果您确实有必要使用此视图，则可以创建一个视图以相同的方式获取信息.我不确定为什么会这么做.

You can then create a view in order to get the information in the same manner, if you really feel the need to use this. I'm not certain why you would.

create or replace view AccountAuthentications as
 select account_id, regular_id, null
   from user_accounts
  union all
 select account_id, null, open_id
   from openid_account_auth;

简而言之，除非受到严格限制，否则应始终在自然级别存储数据并使用数据库来确保保持完整性.如果随后需要稍微不同地使用数据，则可以使用视图，或实例化视图等.

Simply put, unless under severe constraints, you should always store data at it's natural level and use the database to ensure that integrity is maintained. If you then need to use the data slightly differently you can use views, or materialized views etc., to do so.

_{1.很抱歉，但是我无法让自己在每个表的名称前加上tbl_.}

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