Home » SQL & PL/SQL » SQL & PL/SQL » need help in setting constraints for the column based on subquery (2 threads merged by bb) (sql,oracle 11g)
need help in setting constraints for the column based on subquery (2 threads merged by bb) [message #661415] Thu, 16 March 2017 23:13 Go to next message
sowk
Messages: 11
Registered: March 2017
Junior Member
Hi all,

I have following tables

SQL> select * from team;

TEAMID TEAMN CITY COACH
---------- ----- ---------- ----------
1 abc mangalore jins
2 xyz bangalore vikesh
3 mnq mumbai tejas

SQL> select * from matchsheduled;

HOSTTEAM GUESTTEAM MATCHID STADIUMID MATCHDATE MANOFTHEMATCH MATCHWON
---------- ---------- ---------- ---------- --------- ------------- ----------
1 2 21 11 12-MAR-17 222 2

SQL> select * from player;

PLAYERID NAME AGE TEAMID
---------- ---------- ---------- ----------
111 adithya 23 1
222 jithesh 24 2
333 reyvan 21 3

I am trying to set the constraint for MANOFTHEMATCH column of table matchsheduled. MANOFTHEMATCH is the foreign key referring PLAYERID of player table.My constraint is MANOFTHEMATCH value should be the playerid of either hostteam or guestteam.How to check this constraint?
If if try to enter MANOFTHEMATCH as 333 it should restrict as 333 is the player of team 3 which is not in host team(1) nor guest team(2)


need help in SQL Check constraint on column referencing other tables [message #661416 is a reply to message #661415] Thu, 16 March 2017 23:57 Go to previous messageGo to next message
sowk
Messages: 11
Registered: March 2017
Junior Member
Hi all,

I have the following table

SQL> select * from team;

    TEAMID TEAMN CITY       COACH
---------- ----- ---------- ----------
         1 abc   mangalore  jins
         2 xyz   bangalore  vikesh
         3 mnq   mumbai     tejas

SQL> select * from matchsheduled;

  HOSTTEAM  GUESTTEAM    MATCHID  STADIUMID MATCHDATE MANOFTHEMATCH   MATCHWON
---------- ---------- ---------- ---------- --------- ------------- ----------
         1          2         21         11 12-MAR-17           222          2

SQL> select * from player;

  PLAYERID NAME              AGE     TEAMID
---------- ---------- ---------- ----------
       111 adithya            23          1
       222 jithesh            24          2
       333 reyvan             21          3

I need to give check constraint for MANOFTHEMATCH column referring player table.while entering values for MANOFTHEMATCH it should restrict the user to enter playerid of the team other than guestteam and host team. should I use trigger or cursur for solving the problem? and need solution.

I tried creating view to extract all player from guest team and hostteam


create view matchplayer as(select distinct(PLAYERID) mplayer from player,matchsheduled where TEAMID=HOSTTEAM
union
select distinct(PLAYERID) mplayer from player,matchsheduled where TEAMID=GUESTTEAM);

then trying to create trigger as follows

SQL> CREATE OR REPLACE TRIGGER t3
  2  AFTER INSERT OR UPDATE
  3     ON matchsheduled
  4     FOR EACH ROW
  5  BEGIN
  6  if :new.MANOFTHEMATCH in (select mplayer from matchplayer) then
  7  INSERT INTO matchsheduled
  8     ( HOSTTEAM,
  9  GUESTTEAM,
 10  MATCHID,
 11  STADIUMID,
 12  MATCHDATE,
 13  MANOFTHEMATCH,
 14  MATCHWON
 15        )
 16     VALUES
 17     ( :new.HOSTTEAM,
 18       :new.GUESTTEAM,
 19       :new.MATCHID,
 20       :new.STADIUMID,
 21       :new.MATCHDATE,
 22       :new.MANOFTHEMATCH,
 23       :new.MATCHWON
 24       );
 25  end if;
 26  end;
 27  /

Warning: Trigger created with compilation errors.


what is the syntax for writing trigger in this case?

[Updated on: Fri, 17 March 2017 01:49]

Report message to a moderator

Re: need help in SQL Check constraint on column referencing other tables [message #661419 is a reply to message #661416] Fri, 17 March 2017 02:26 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8713
Registered: November 2002
Location: California, USA
Senior Member
I suppose there are various ways to do this. Since you have chosen a trigger, I will post a working version of that. There is no view necessary.

-- test environment:
SCOTT@orcl_12.1.0.2.0> CREATE TABLE player
  2    (playerid       NUMBER,
  3  	name	       VARCHAR2(7),
  4  	age	       NUMBER,
  5  	teamid	       NUMBER)
  6  /

Table created.

SCOTT@orcl_12.1.0.2.0> INSERT ALL
  2  INTO player VALUES (111, 'adithya', 23, 1)
  3  INTO player VALUES (222, 'jithesh', 24, 2)
  4  INTO player VALUES (333, 'reyvan', 21,  3)
  5  SELECT * FROM DUAL
  6  /

3 rows created.

SCOTT@orcl_12.1.0.2.0> SELECT * FROM player
  2  /

  PLAYERID NAME           AGE     TEAMID
---------- ------- ---------- ----------
       111 adithya         23          1
       222 jithesh         24          2
       333 reyvan          21          3

3 rows selected.

SCOTT@orcl_12.1.0.2.0> CREATE TABLE matchscheduled
  2    (hostteam       NUMBER,
  3  	guestteam      NUMBER,
  4  	matchid        NUMBER,
  5  	stadiumid      NUMBER,
  6  	matchdate      DATE,
  7  	manofthematch  NUMBER,
  8  	matchwon       NUMBER)
  9  /

Table created.

-- trigger:
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE TRIGGER matchscheduled_bir
  2    BEFORE INSERT ON matchscheduled
  3    FOR EACH ROW
  4  DECLARE
  5    v_count	NUMBER;
  6  BEGIN
  7    SELECT COUNT(*)
  8    INTO   v_count
  9    FROM   player
 10    WHERE  playerid = :NEW.manofthematch
 11    AND    (teamid = :NEW.guestteam OR teamid = :NEW.hostteam);
 12    IF v_count = 0 THEN
 13  	 RAISE_APPLICATION_ERROR
 14  	   (-20001,
 15  	    'no match for manofthematch and hostteam or guestteam in playerid and teamid');
 16    END IF;
 17  END matchscheduled_bir;
 18  /

Trigger created.

SCOTT@orcl_12.1.0.2.0> SHOW ERRORS
No errors.

-- insert that fails:
SCOTT@orcl_12.1.0.2.0> INSERT INTO matchscheduled (guestteam, hostteam, manofthematch) VALUES (1, 2, 333)
  2  /
INSERT INTO matchscheduled (guestteam, hostteam, manofthematch) VALUES (1, 2, 333)
            *
ERROR at line 1:
ORA-20001: no match for manofthematch and hostteam or guestteam in playerid and
teamid
ORA-06512: at "SCOTT.MATCHSCHEDULED_BIR", line 10
ORA-04088: error during execution of trigger 'SCOTT.MATCHSCHEDULED_BIR'


SCOTT@orcl_12.1.0.2.0> SELECT guestteam, hostteam, manofthematch FROM matchscheduled
  2  /

no rows selected

-- insert that does not fail:
SCOTT@orcl_12.1.0.2.0> INSERT INTO matchscheduled (guestteam, hostteam, manofthematch) VALUES (1, 2, 222)
  2  /

1 row created.

SCOTT@orcl_12.1.0.2.0> SELECT guestteam, hostteam, manofthematch FROM matchscheduled
  2  /

 GUESTTEAM   HOSTTEAM MANOFTHEMATCH
---------- ---------- -------------
         1          2           222

1 row selected.
Re: need help in SQL Check constraint on column referencing other tables [message #661430 is a reply to message #661419] Fri, 17 March 2017 03:52 Go to previous messageGo to next message
sowk
Messages: 11
Registered: March 2017
Junior Member
Thank you for the solution. I am eager to learn other possible ways of solving problem without trigger.
Re: need help in SQL Check constraint on column referencing other tables [message #661458 is a reply to message #661430] Fri, 17 March 2017 23:24 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8713
Registered: November 2002
Location: California, USA
Senior Member
Here is another method that uses a function, a virtual column, and a check constraint. It requires that the function be deterministic, which means that your player table must be static or you may get incorrect results based on previous results.


-- test environment:
SCOTT@orcl_12.1.0.2.0> CREATE TABLE player
  2    (playerid       NUMBER,
  3  	name	       VARCHAR2(7),
  4  	age	       NUMBER,
  5  	teamid	       NUMBER)
  6  /

Table created.

SCOTT@orcl_12.1.0.2.0> INSERT ALL
  2  INTO player VALUES (111, 'adithya', 23, 1)
  3  INTO player VALUES (222, 'jithesh', 24, 2)
  4  INTO player VALUES (333, 'reyvan', 21,  3)
  5  SELECT * FROM DUAL
  6  /

3 rows created.

SCOTT@orcl_12.1.0.2.0> SELECT * FROM player
  2  /

  PLAYERID NAME           AGE     TEAMID
---------- ------- ---------- ----------
       111 adithya         23          1
       222 jithesh         24          2
       333 reyvan          21          3

3 rows selected.

SCOTT@orcl_12.1.0.2.0> CREATE TABLE matchscheduled
  2    (hostteam       NUMBER,
  3  	guestteam      NUMBER,
  4  	matchid        NUMBER,
  5  	stadiumid      NUMBER,
  6  	matchdate      DATE,
  7  	manofthematch  NUMBER,
  8  	matchwon       NUMBER)
  9  /

Table created.

-- function, virtual column, and check constraint:
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE FUNCTION check_matchscheduled
  2    (p_manofthematch IN matchscheduled.manofthematch%TYPE,
  3  	p_guestteam	IN matchscheduled.guestteam%TYPE,
  4  	p_hostteam	IN matchscheduled.hostteam%TYPE)
  5    RETURN NUMBER DETERMINISTIC
  6  AS
  7    v_count	NUMBER;
  8  BEGIN
  9    SELECT COUNT(*)
 10    INTO   v_count
 11    FROM   player
 12    WHERE  playerid = p_manofthematch
 13    AND    (teamid = p_guestteam OR teamid = p_hostteam);
 14    RETURN v_count;
 15  END check_matchscheduled;
 16  /

Function created.

SCOTT@orcl_12.1.0.2.0> SHOW ERRORS
No errors.
SCOTT@orcl_12.1.0.2.0> ALTER TABLE matchscheduled ADD
  2    (virtual_check_col NUMBER GENERATED ALWAYS AS
  3  	 (check_matchscheduled(manofthematch, guestteam, hostteam)) VIRTUAL)
  4  /

Table altered.

SCOTT@orcl_12.1.0.2.0> ALTER TABLE matchscheduled ADD CONSTRAINT matchschduled_ck CHECK (virtual_check_col != 0)
  2  /

Table altered.

-- insert that fails:
SCOTT@orcl_12.1.0.2.0> INSERT INTO matchscheduled (guestteam, hostteam, manofthematch) VALUES (1, 2, 333)
  2  /
INSERT INTO matchscheduled (guestteam, hostteam, manofthematch) VALUES (1, 2, 333)
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.MATCHSCHDULED_CK) violated


SCOTT@orcl_12.1.0.2.0> SELECT guestteam, hostteam, manofthematch FROM matchscheduled
  2  /

no rows selected

-- insert that does not fail:
SCOTT@orcl_12.1.0.2.0> INSERT INTO matchscheduled (guestteam, hostteam, manofthematch) VALUES (1, 2, 222)
  2  /

1 row created.

SCOTT@orcl_12.1.0.2.0> SELECT guestteam, hostteam, manofthematch FROM matchscheduled
  2  /

 GUESTTEAM   HOSTTEAM MANOFTHEMATCH
---------- ---------- -------------
         1          2           222

1 row selected.
Re: need help in SQL Check constraint on column referencing other tables [message #661459 is a reply to message #661458] Sat, 18 March 2017 00:22 Go to previous messageGo to next message
sowk
Messages: 11
Registered: March 2017
Junior Member
virtual column implementation is new for me.using function and virtual column also. thanks a lot.

Re: need help in setting constraints for the column based on subquery (2 threads merged by bb) [message #661461 is a reply to message #661415] Sun, 19 March 2017 03:29 Go to previous messageGo to next message
John Watson
Messages: 6973
Registered: January 2010
Location: Global Village
Senior Member
I think that what you have hit is a not-uncommon problem: Oracle does not support what are called "assertions". Assertions are defined in the SQL-92 standard, some people call them cross-row constraints or multi-table check constraints. I think some third party databases (MySQL, perhaps?) have them already. Barbara has come up with a nice solution.
Re: need help in SQL Check constraint on column referencing other tables [message #661465 is a reply to message #661416] Sun, 19 March 2017 07:53 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2614
Registered: January 2010
Location: Connecticut, USA
Senior Member
You can use Barbara's solutions or (if you are on older version) you can denormalize and use constraints:

SQL> CREATE TABLE player
  2      (
  3       playerid       NUMBER,
  4     name           VARCHAR2(7),
  5     age            NUMBER,
  6     teamid         NUMBER
  7      )
  8  /

Table created.

SQL> INSERT ALL
  2    INTO player VALUES (111, 'adithya', 23, 1)
  3    INTO player VALUES (222, 'jithesh', 24, 2)
  4    INTO player VALUES (333, 'reyvan', 21,  3)
  5    SELECT * FROM DUAL
  6  /

3 rows created.

SQL> ALTER TABLE player
  2    ADD CONSTRAINT player_pk
  3      PRIMARY KEY(playerid)
  4  /

Table altered.

SQL> ALTER TABLE player
  2    ADD CONSTRAINT player_uk
  3      UNIQUE(playerid,teamid)
  4  /

Table altered.

SQL> CREATE TABLE matchscheduled
  2      (
  3     hostteam              NUMBER,
  4     guestteam             NUMBER,
  5     matchid               NUMBER,
  6     stadiumid             NUMBER,
  7     matchdate             DATE,
  8     manofthematch         NUMBER,
  9     manofthematch_teamid  NUMBER,
 10     matchwon              NUMBER
 11      )
 12  /

Table created.

SQL> ALTER TABLE matchscheduled
  2    ADD CONSTRAINT matchscheduled_chk
  3      CHECK(
  4               (manofthematch is null and manofthematch_teamid is null)
  5            OR
  6               (manofthematch is not null and manofthematch_teamid in (hostteam,guestteam))
  7            )
  8  /

Table altered.

SQL> ALTER TABLE matchscheduled
  2    ADD CONSTRAINT matchscheduled_fk
  3      FOREIGN KEY(manofthematch,manofthematch_teamid)
  4      REFERENCING player(playerid,teamid)
  5  /

Table altered.

SQL> INSERT INTO matchscheduled (guestteam, hostteam, manofthematch, manofthematch_teamid) VALUES (1, 2, 333, 3)
  2  /
INSERT INTO matchscheduled (guestteam, hostteam, manofthematch, manofthematch_teamid) VALUES (1, 2, 333, 3)
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.MATCHSCHEDULED_CHK) violated


SQL> INSERT INTO matchscheduled (guestteam, hostteam, manofthematch, manofthematch_teamid) VALUES (1, 2, 222, 3)
  2  /
INSERT INTO matchscheduled (guestteam, hostteam, manofthematch, manofthematch_teamid) VALUES (1, 2, 222, 3)
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.MATCHSCHEDULED_CHK) violated


SQL> INSERT INTO matchscheduled (guestteam, hostteam, manofthematch, manofthematch_teamid) VALUES (1, 2, 333, 2)
  2  /
INSERT INTO matchscheduled (guestteam, hostteam, manofthematch, manofthematch_teamid) VALUES (1, 2, 333, 2)
*
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.MATCHSCHEDULED_FK) violated - parent key
not found


SQL> INSERT INTO matchscheduled (guestteam, hostteam, manofthematch, manofthematch_teamid) VALUES (1, 2, 222, 2)
  2  /

1 row created.

SQL>

And, btw, Barbara defines manofthematch as nullable while her solutions assume manofthematch is not null and will raise an error if manofthematch is null. You might need to adjust Barbara's solutions if there was no manofthematch (both teams showed lousy play Mad).

SY.

[Updated on: Sun, 19 March 2017 07:55]

Report message to a moderator

Re: need help in SQL Check constraint on column referencing other tables [message #661476 is a reply to message #661465] Sun, 19 March 2017 23:04 Go to previous messageGo to next message
sowk
Messages: 11
Registered: March 2017
Junior Member
Hi Solomon Yakobson

I understand your point in de-normalize design. but when we have stored guestteam and hostteam added in the table what is the need of one more column as manofthematchteamid.

Here my constraint is the player to whom I give manofthematch award should be either from hostteam or from guest team.

My question is if we denormalize will it not voilate the design guideline? will it not store duplicate entries?

Re: need help in SQL Check constraint on column referencing other tables [message #661479 is a reply to message #661476] Mon, 20 March 2017 06:59 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2614
Registered: January 2010
Location: Connecticut, USA
Senior Member
You can't create FK without it since you don't know if manofthematch is hostteam or guest team player.

SY.
Re: need help in SQL Check constraint on column referencing other tables [message #661491 is a reply to message #661465] Tue, 21 March 2017 06:14 Go to previous messageGo to next message
sowk
Messages: 11
Registered: March 2017
Junior Member
Thank you all.

Need help in writing query for

Display the team where all its won matches played in the same stadium.



SQL> select * from matchsheduled;

  HOSTTEAM  GUESTTEAM    MATCHID  STADIUMID MATCHDATE MANOFTHEMATCH   MATCHWON
---------- ---------- ---------- ---------- --------- ------------- ----------
         4          5       3333         11 21-MAR-17           444          5
         4          5       4444         11 12-MAR-17           444          4
         1          2       1111         11 18-MAR-17           111          1
         1          3       2222         22 10-MAR-17           333          1
         1          4       5555         11 18-APR-17           999          4
         2          4       6666         11 12-MAY-17           999          4


in the above case i should get matchwon as
5
4

[Updated on: Tue, 21 March 2017 06:15]

Report message to a moderator

Re: need help in SQL Check constraint on column referencing other tables [message #661492 is a reply to message #661491] Tue, 21 March 2017 06:15 Go to previous messageGo to next message
John Watson
Messages: 6973
Registered: January 2010
Location: Global Village
Senior Member
What SQL have you tried so far? (I hope you aren't asking people to do your college homework for you.)
Re: need help in SQL Check constraint on column referencing other tables [message #661493 is a reply to message #661492] Tue, 21 March 2017 06:20 Go to previous messageGo to next message
sowk
Messages: 11
Registered: March 2017
Junior Member
Yes I am learning SQL
Re: need help in SQL Check constraint on column referencing other tables [message #661494 is a reply to message #661493] Tue, 21 March 2017 06:24 Go to previous message
John Watson
Messages: 6973
Registered: January 2010
Location: Global Village
Senior Member
You won't learn much if you copy answers provided by other people. Indeed, one could describe that as "cheating". Have a go yourself first. You might start by refining the question. For example, your team 4 has been both a host and a guest at stadium 11, which doesn't sound possible to me.
Previous Topic: Append Hint in oracle
Next Topic: Conditional Join Logic
Goto Forum:
  


Current Time: Wed Jun 28 21:19:10 CDT 2017

Total time taken to generate the page: 0.06169 seconds