Oracle Database Enforce CHECK在多个表上 [英] Oracle Database Enforce CHECK on multiple tables

问题描述

我试图在多个表的ORACLE数据库中强制执行CHECK约束

I am trying to enforce a CHECK Constraint in a ORACLE Database on multiple tables

CREATE TABLE RollingStocks ( 
  Id NUMBER,
  Name Varchar2(80) NOT NULL,           
  RollingStockCategoryId NUMBER NOT NULL,            
  CONSTRAINT Pk_RollingStocks Primary Key (Id),
  CONSTRAINT Check_RollingStocks_CategoryId  
  CHECK ((RollingStockCategoryId  IN (SELECT Id FROM FreightWagonTypes)) 
        OR 
        (RollingStockCategoryId  IN (SELECT Id FROM LocomotiveClasses)))       
);

...但是出现以下错误:

...but i get the following error:

*原因:语句中的此处不允许子查询. *操作:从语句中删除子查询.

*Cause: Subquery is not allowed here in the statement. *Action: Remove the subquery from the statement.

您能帮助我理解问题出在哪里或如何获得相同的结果吗?

Can you help me understanding what is the problem or how to achieve the same result?

推荐答案

在Oracle中，检查约束非常有限.要按照您的建议进行检查，您必须实现 PL/SQL触发器.

Check constraints are very limited in Oracle. To do a check like you propose, you'd have to implement a PL/SQL trigger.

我的建议是完全避免触发.实现一个存储过程，该过程可以修改数据库并包括检查.尽管存储过程难以实现，但存储过程更易于维护.但是从长远来看，将前端从直接表访问更改为存储过程访问会获得很多回报.

My advise would be to avoid triggers altogether. Implement a stored procedure that modifies the database and includes the checks. Stored procedures are easier to maintain, although they are slightly harder to implement. But changing a front end from direct table access to stored procedure access pays back many times in the long run.

这篇关于Oracle Database Enforce CHECK在多个表上的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！