And So I was attempting to do this however it appears the last line (the check) does not allow sub queries inside it. In whatever way to make this happen Oracle?
CREATE TABLE Tank ( n_id int, day date, level int, CONSTRAINT pk_w_td PRIMARY KEY (n_id,day), CONSTRAINT fk_w_td_tan FOREIGN KEY (n_id) REFERENCES Tanks ON DELETE CASCADE, CHECK (level > 0 AND level <= (SELECT capacity FROM Tanks WHERE Tanks.n_id = TanksDay.n_id)) );
This is actually the error info:
Error at Command Line:7 Column:32 Error report: SQL Error: ORA-02251: subquery not allowed here 02251. 00000 - "subquery not allowed here" *Cause: Subquery is not allowed here in the statement. *Action: Remove the subquery from the statement.
You will find three fundamental methods to solve this kind of problem since CHECK constraints can't be with different query.
Option 1: Triggers
Probably the most simplistic approach is always to put a trigger on TANK that queries TANKS and throws the best when the LEVEL surpasses CAPACITY. The issue with this particular kind of simplistic approach, though, is it is almost impossible to deal with concurrency issues properly. If session 1 lessens the CAPACITY, then session 2 boosts the LEVEL, after which both transactions commit, triggers won't have the ability to identify the breach. It isn't really an problem if either from the tables are rarely modified, however in general it will likely be an problem.
Option 2: Materialized sights
You are able to solve the concurrency problem by creating an ON COMMIT materialized view that joins the TANK and TANKS table after which developing a CHECK constraint around the materialized view that certifies the LEVEL <= CAPACITY. You may also avoid storing the information two times by getting the materialized view contain just data that will violate the constraint. This can require materialized view logs on the bottom tables that will add a little of overhead to card inserts (though under using triggers). Pushing the check to commit-time will solve the concurrency problem however it introduces a little of the exception management problem because the COMMIT operation are now able to fail since the materialized view refresh unsuccessful. The application will have to have the ability to handle this problem and also to alert the consumer to that particular fact.
Option 3: Alter the data model
For those who have something in table A that is dependent on the limit in table B, that could indicate the limit in B needs to be a characteristic of table A (rather than or additionally to becoming an attribute of table B). It is dependent around the more knowledge about your computer data model, obviously, but it is frequently worth thinking about.
No regrettably CHECK constraints cannot contain subqueries - see documentation.
You'll most likely have to create triggers and employ
RAISE_APPLICATION_ERROR whether it's outdoors the permitted range.
Justin's answer has good quality ideas. A different one would be to wrap all card inserts/updates towards the table having a package (a TAPI, for a moment), and implement the inspections there. You will need to make sure that all programs make use of your TAPI. You'll should also implement some custom securing to safeguard the constraint in the results of concurrent activity.