Using Subquery in a Check Statement in Oracle

Using subquery in a Check statement in Oracle

There are three basic ways to solve this sort of problem since CHECK constraints cannot be based on a query.

Option 1: Triggers

The most simplistic approach would be to put a trigger on TANK that queries TANKS and throws an exception if the LEVEL exceeds CAPACITY. The problem with this sort of simplistic approach, though, is that it is nearly impossible to handle concurrency issues correctly. If session 1 decreases the CAPACITY, then session 2 increases the LEVEL, and then both transactions commit, triggers will not be able to detect the violation. This may not be an issue if one or both of the tables are seldom modified, but in general it's going to be an issue.

Option 2: Materialized views

You can solve the concurrency issue by creating an ON COMMIT materialized view that joins the TANK and TANKS table and then creating a CHECK constraint on the materialized view that verifies that the LEVEL <= CAPACITY. You can also avoid storing the data twice by having the materialized view contain just data that would violate the constraint. This will require materialized view logs on both the base tables which will add a bit of overhead to inserts (though less than using triggers). Pushing the check to commit-time will solve the concurrency issue but it introduces a bit of an exception management issue since the COMMIT operation can now fail because the materialized view refresh failed. Your application would need to be able to handle that problem and to alert the user to that fact.

Option 3: Change the data model

If you have a value in table A that depends on a limit in table B, that may indicate that the limit in B ought to be an attribute of table A (instead of or in addition to being an attribute of table B). It depends on the specifics of your data model, of course, but it's often worth considering.

Oracle SQL - Can I use sub-query in a CHECK constraint in DDL?

It's better to ask the documentation about that:

Restrictions on Check Constraints

Check constraints are subject to the following restrictions:

• Conditions of check constraints cannot contain the following constructs:
• 
  
  • Subqueries and scalar subquery expressions
• 
  
  • Calls to user-defined functions

Oracle SQL - Subquery Works fine, However Create Table with that subquery appears to hang

So the answer to this one.

CREATE TABLE <Table Name> AS 
(
   SELECT foo 
   FROM baa
   LEFT JOIN 
   ( SELECT foo FROM baa WHERE DATES BETWEEN SYSDATE AND SYSDATE - 100 )
   WHERE DATES_1 BETWEEN SYSDATE - 10 AND SYSDATE - 100
)  

The problem was that the BETWEEN statements did not match the same time period and the sub query was looking at more data than the main query (I guess this was causing a full scan over the tables?)

The below query has the matching between statement time period and this returned the results in less than 3 minutes.

CREATE TABLE <Table Name> AS 
(
   SELECT foo FROM baa
   LEFT JOIN ( SELECT foo FROM baa WHERE DATES BETWEEN SYSDATE - 10 AND SYSDATE - 100 )
   WHERE DATES_1 BETWEEN SYSDATE - 10 AND SYSDATE - 100
)  

How can I create constraint using subquery in sql?

SQL Assertions

The feature you're looking for is called SQL assertions, and it's not yet implemented in Oracle 12c. Meanwhile, use a trigger, as you've suggested yourself.

Your trigger

Of course, your trigger doesn't work because its syntax is quite wrong.

CREATE TRIGGER xx_game_trigger
BEFORE INSERT          -- This clause
ON xx_game             -- before this one
REFERENCING NEW AS new -- You'll probably need this
FOR EACH ROW
BEGIN
  -- There's no such thing as IF EXISTS in PL/SQL. Here's a workaround. This loop will run
  -- zero or one times.
  FOR rec IN (
    SELECT 1 FROM dual
    WHERE EXISTS (
      -- I'm assuming that you're interested in matches between existing records
      -- And the record you're about to insert (:new.xxx). Adapt accordingly
      SELECT 1 FROM xx_game WHERE (home_team,play_date) IN (:new.away_team,:new.play_date)
    )
    OR EXISTS (
      SELECT 1 FROM xx_game WHERE (away_team,play_date) IN (:new.home_team,:new.play_date)
    )
  )
  LOOP
    -- There's no TRANSACTION keyword here. But anyway, I'd rather raise an exception
    -- than roll back the transaction. That seems much cleaner to me.
    ROLLBACK;
  END LOOP;
END xx_game_trigger;

Please consider the Oracle documentation for the complete CREATE TRIGGER syntax

Sub queries in check constraint

Note, what you really want is a foreign key constraint. That said, to get a "query" into a check you can write a function that contains the query and outputs a scalar value, then use that function in the check constraint.

CREATE FUNCTION myFunction (
    @field DATATYPE(?)
)
RETURNS VARCHAR(5)
AS
BEGIN
    IF EXISTS (SELECT* FROM Table2 WHERE MYFIELD = @field)
        return 'True'
    return 'False'
END

Something like that. Not tested.

Then you can add it to your check like so

ALTER TABLE Table1
    WITH CHECK ADD CONSTRAINT CK_Code
    CHECK (myFunction(MYFIELD) = 'True')

Sub query in check not allowed here

One way to solve this is with the data model. Use an intersection table to join WORKER and STATION.

The trick is to create unique composite keys which add STATE to the actual primary keys of WORKER and STATION.

So, something like this:

alter table WORKER add constraint wrk_state_uk unique (worker_id, employing_state) ;

alter table STATION add constraint stn_state_uk unique (station_id, state) ;

Then we create an intersecting table with lots of constraints :). ( Obviously I'm taking a guess at the datatypes here, and the column names too.)

create table station_maintainer (
    station_id number not null
    , station_state number not null
    , worker_id number not null
    , employing_state number not null
    , constraint stn_mnt_pk primary key (station_id, worker_id)
    , constraint stn_mnt_uk unique (station_id )
    , constraint stn_mnt_stn_fk foreign key (station_id)
          references station (station_id)
    , constraint stn_mnt_wrk_fk foreign key (worker_id)
          references worker (worker_id)
    , constraint stn_mnt_state_ck check (station_state = employing_state)

;

The UNIQUE key ensures each station has only one maintainer. I presumer a work can maintain more than one station,

We need the unique keys on the two tables so that the foreign keys can reference the states as well as the actual primary keys. This is a dodge but it does ensure that the STATE columns on the intersection table match the STATE columns on the parents.

---

The actual tables in the CHECK you posted are confused. There's nothing joining WORKER and STATE to STATION, soI have made a guess as to how the tables may be related. Please don't complain if my guess does not match your actual model: instead, amend your question to provide accurate and sufficient detail.

How can I check if one subquery is a subset of another subquery in Oracle SQL?

So, I believe I found an answer.
First, note that if B ⊆ A then B-A = ∅

Then, using only SQL set operators, I can do

select Foo.f
from Foo
where
    not exists (
        --Subset B
        (select...)

        minus

        --Subset A
        (select...));

Perform a query with a variable as subquery

You can achieve something similar with WITH clause:

with veryLongQueryVar as
 (select SOMECOLUMN from VERY_LONG_QUERY WHERE SOMECHECK = 'some check')
select * from SOURCE
where A = 'a'
 and B in (select SOMECOLUMN from veryLongQueryVar)

You can also use SELECT INTO to select your very long query results into variable (of type table of something) and the use table to pass it into IN. But that would result in two separate queries and it seems you want to avoid that.

---

Related Topics