Oracle Database Enforce CHECK on multiple tables

Question

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?

Answer 1

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.

Answer 2

What you are trying to is ensure that the values inserted in one table exist in another table i.e. enforce a foreign key. So that would be :

CREATE TABLE RollingStocks ( 
...

  CONSTRAINT Pk_RollingStocks Primary Key (Id),
  CONSTRAINT RollingStocks_CategoryId_FK (RollingStockCategoryId )
     REFERENCES FreightWagonTypes (ID)      
);  

Except that you want to enforce a foreign key which references two tables. This cannot be done.

You have a couple of options. One would be to merge FreightWagonTypes and LocomotiveClasses into a single table. If you need separate tables for other parts of your application then you could build a materialized view for the purposes of enforcing the foreign key. Materialized Views are like tables and can be referenced by foreign keys. This option won't work if the key values for the two tables clash.

Another option is to recognise that the presence of two candidate referenced tables suggests that RollingStock maybe needs to be split into two tables - or perhaps three: a super type and two sub-type tables, that is RollingStock and FreightWagons, Locomotives.

By the way, what about PassengerCoaches, GuardsWagons and RestaurantCars?

Answer 3

Oracle doesn't support complex check constraints like that, unfortunately.

In this case, your best option is to change the data model a bit - add a parent table over FreightWagonTypes and LocomotiveClasses, which will hold all the ids from both of these tables. That way you can add a FK to a single table.