How can I create constraint using subquery in sql?

Question

Here is my table game :

create table game (
    h_team number,
     a_team number,
     p_date date
);

Condition to be followed: Every team plays a single game on a particular date. Basically normal rules that usually should happen for a tournament.

I have added following constraints:

I want to add another constraints which restricts to add what the following queries performs:

select h_team, p_date
from game
where (h_team,p_date) not in (select a_team,p_date from game);

select a_team, p_date
from game
where (a_team,p_date) not in (select h_team,p_date from game);

For example,Suppose a record in that table is (1,2,23-JAN-2000). So records like (3,1,23-JAN-2000), (2,4,23-JAN-2000) etc. cannot be inserted. Thanks!

I preferred in SQl but it seems it is not possible in SQL. So How will it be using PL-SQL.

I removed the incompatible database tags. Please tag only with the database you are really using. — Gordon Linoff, Jul 17 '17 at 11:35
Possible duplicate of [Using subquery in a Check statement in Oracle](https://stackoverflow.com/questions/4071024/using-subquery-in-a-check-statement-in-oracle) — JeffUK, Jul 17 '17 at 11:55
You can't use queries in constraints. Can you explain in words the scenario you want to enforce or prevent? — William Robertson, Jul 17 '17 at 13:03
@WilliamRobertson Suppose a record in that table is (1,2,23-JAN-2000). So records like (3,1,23-JAN-2000), (2,4,23-JAN-2000) etc. cannot be inserted. — SKY, Jul 17 '17 at 13:41
Thanks, see what you mean now. Unfortunately this is not possible in a constraint. Would a materialized view do? — William Robertson, Jul 17 '17 at 14:13
`create trigger xx_game_trigger on xx_game before insert as if exists( select home_team,play_date from xx_game where (home_team,play_date) in (away_team,play_date) or select away_team,play_date fromm xx_game where (away_team,play_date) in (home_team,play_date)) BEGIN ROLLBACK TRANSACTION; RETURN END;` I tried this but it's throwing a syntax error. — SKY, Jul 17 '17 at 14:21

Lukas Eder · Accepted Answer · 2017-07-18T08:35:15.850

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

But that code was giving an error. Do you have any idea how to fix it. — SKY, Jul 17 '17 at 15:14
Hey @Lukas! Appreciate your effort, man. But I got the same error : `ORA-04071: missing BEFORE, AFTER or INSTEAD OF keyword 04071. 00000 - "missing BEFORE, AFTER or INSTEAD OF keyword" *Cause: The trigger statement is missing the BEFORE/AFTER/INSTEAD OF clause. *Action: Specify either BEFORE, AFTER or INSTEAD OF.` — SKY, Jul 18 '17 at 08:49

How can I create constraint using subquery in sql?

1 Answers1

SQL Assertions

Your trigger