Oracle Sql Check Constraint

Question

What I want to do is simple and below are details. I have two tables.

Create Table Event(
IDEvent number (8) primary key,
StartDate date not null,
EndDate date not null
);

This is fine.

Here is second table.

Create Table Game(
IDGame number (8) primary key,
GameDate date not null,
constraint checkDate 
check (GameDate >= to_date(StartDate references from Event(StartDate)))
);

The constraint checkDate is to check if the date is bigger than the startdate. While checking I'm getting error : Missing right parenthesis.

My question is, If this is possible to do then why it is giving me an error?

So, if there are say 5 records in Event which StartDate do you want to compare to GameDate? — René Nyffenegger
– René Nyffenegger, Commented Jan 22, 2012 at 7:11

Raihan · Accepted Answer · 2012-01-22 04:20:30Z

4

A check constraint in a table can only verify conditions on the columns of that particular table. You can not refer to columns from other tables.

If you need to verify conditions that involves columns from a different table, you can do it from a before insert/update trigger on that table.

answered Jan 22, 2012 at 4:20

Raihan

10.5k5 gold badges31 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

P3druh77 Over a year ago

Yeah need a trigger :( Thanks for the explanation Raihan

Rob van Wijk · Accepted Answer · 2012-01-22 08:31:51Z

What you want to do is far from simple.

The syntax you propose, doesn't work on any RDBMS. It would be nice to have, but none of the RDBMS vendors have implemented it, because it enforcing such a cross table integrity rule would mean locking the referenced table while updating the game table. If you try to build it yourself, you'll have to do the locking yourself. You'll have to take into account all actions that could possibly violate your rule, such as:

inserting a game
updating the gamedate to a less recent date
updating the event startdate to a more recent date
deleting an event

And for each of these actions you'll have to think of writing code that is multi user proof, by locking the right records in the other table.

If you want to reduce this complexity, you might want to look at a product called RuleGen (www.rulegen.com)

Or you may want to build a specific API and include the checks in just the right places. You'll still have to manually lock yourself in this scenario.

Hope this helps.

Regards,
Rob.

score 0 · Accepted Answer · 2012-01-22 13:09:49Z

0

There is one hack that you can make, but I doubt that performance of inserting games or events will be acceptable, once the tables grow to a certain size:

CREATE TABLE Event 
(
  IDEvent     NUMBER(8) PRIMARY KEY,
  StartDate   DATE NOT NULL,
  EndDate     DATE NOT NULL
);

CREATE TABLE Game 
(
  IDGame     NUMBER(8) PRIMARY KEY,
  GameDate   DATE NOT NULL,
  eventid    NUMBER(8),   -- this is different to your table definition
  CONSTRAINT fk_game_event FOREIGN KEY (eventid) REFERENCES event (idevent)
);

CREATE INDEX game_eventid ON game (eventid);

CREATE MATERIALIZED VIEW LOG ON event 
   WITH ROWID, SEQUENCE (idevent, startdate) INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON game 
   WITH ROWID, SEQUENCE (idgame, eventid, gamedate) INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW mv_event_game
REFRESH FAST ON COMMIT WITH ROWID
AS
SELECT ev.idevent, 
       ev.startdate, 
       g.gamedate
FROM event ev, game g
WHERE g.eventid = ev.idevent;  

ALTER TABLE mv_event_game 
  ADD CONSTRAINT check_game_start check (gamedate >= startdate);

Now any transaction that inserts a game that starts before the referenced event will throw an error when trying to commit the transaction:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning and OLAP options

SQL> INSERT INTO event
  2  (idevent, startdate, enddate)
  3  values
  4  (1, date '2012-01-22', date '2012-01-24');

1 row created.

SQL>
SQL> INSERT INTO game
  2  (idgame, eventid, gamedate)
  3  VALUES
  4  (1, 1, date '2012-01-01');

1 row created.

SQL> commit;
commit
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-02290: check constraint (FOOBAR.CHECK_GAME_START) violated

But again: This will make inserts in both tables slower as the query inside the mview needs to be run each time a commit is performed.

I wasn't able to change the refresh type to FAST which probably would improve commit performance.

edited Jan 22, 2012 at 13:09

answered Jan 22, 2012 at 9:15

user330315

2 Comments

Rob van Wijk Over a year ago

True. This is not feasible performance wise. And during concurrent updates you'll hit some extra contention (rwijk.blogspot.com/2010/01/enq-ji-contention.html). Please note that you can make the materialized view refresh fast when you add rowid's to the join mv. Including new values is not necessary.

user330315 Over a year ago

@RobvanWijk: thanks for the hint regarding fast refresh. I thought I added rowids in one of my tries though (but might have been the logs only)

Collectives™ on Stack Overflow

Oracle Sql Check Constraint

3 Answers 3

1 Comment

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related