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  |
 |
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  |
|
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 |
 |
Barbara Boehmer
Messages: 9088
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 #661458 is a reply to message #661430] |
Fri, 17 March 2017 23:24 |
|
Barbara Boehmer
Messages: 9088
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 #661465 is a reply to message #661416] |
Sun, 19 March 2017 07:53 |
Solomon Yakobson
Messages: 3273
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  ).
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 #661491 is a reply to message #661465] |
Tue, 21 March 2017 06:14 |
|
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 |
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Thu Apr 18 23:02:10 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
