Oracle Database Enforce CHECK on multiple tables

Question

I am trying to provide a CHECK constraint in an 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:

* Reason: The subquery is not specified here. * Action: remove the subquery from the statement.

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

Answer 1

Check restrictions are very limited in Oracle. To do the validation as you suggest, you will need to run PL / SQL .

My recommendation would be to avoid triggers altogether. Implement a stored procedure that modifies the database and includes validations. Stored procedures are easier to maintain, although they are slightly more difficult to implement. But the change in the external interface from direct access to access to the stored procedure in the dining room repeatedly comes back in the long run.

Answer 2

What you are trying to do is make sure that the values ​​inserted in one table exist in another table, that is, forcing a foreign key. So this will be:

 CREATE TABLE RollingStocks ( ... CONSTRAINT Pk_RollingStocks Primary Key (Id), CONSTRAINT RollingStocks_CategoryId_FK (RollingStockCategoryId ) REFERENCES FreightWagonTypes (ID) ); 

Except that you want to apply a foreign key that refers to two tables. This is impossible to do.

You have several options. One could combine FreightWagonTypes and LocomotiveClasses into one table. If you need separate tables for other parts of the application, you can create a materialized view to force the use of a foreign key. Materialized views are similar to tables and can refer to foreign keys. This parameter will not work if the key values ​​for the two tables conflict.

Another option is to recognize that having two tables referenced by the candidates suggests that RollingStock may need to be split into two tables - or perhaps three: supertypes and two subtype tables, which are RollingStock and FreightWagons, Locomotives.

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

Answer 3

Oracle, unfortunately, does not support complex control restrictions.

In this case, your best option is to slightly modify the data model - add a parent table on top of FreightWagonTypes and LocomotiveClasses , which will contain all the identifiers from both of these tables. This way you can add FK to one table.