Solved

oracle constraints on tables

Posted on 2013-01-21
10
379 Views
Last Modified: 2013-01-23
Hi,

I have a tricky question about constraints and primary keys.

lets say i have a table which has the following structure.

Table name : Events

eventid    integer,
type          varchar2(300),
thetime    date,
description varchar2(3000)


so i have a table that stores different types of events that occur at certain times.
Generally speaking the events should be unique by time but there is an exception that one type of event can have multiple entries at the same time with the same description.  Is there a way i can have a unique constraint with exceptions or a check constraint that checks for uniqueness.  I dont really want to do it in a trigger as that will slow down the inserts significantly.  This is a simplified extract of a very large database with a very complex structure and ui so performance is important.
0
Comment
Question by:cesarchavez
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 14

Expert Comment

by:ajexpert
ID: 38803884
You just need to handle UNIQUE CONSTRAINT VIOLATION exception in the code.  So with unique constraint violation exception don't take any action

--pseudo code
BEGIN
INSERT INTO EVENTS (....) VALUES (....)

EXCEPTION
  WHEN <unique constraint violation err>
      NULL;
  WHEN OTHERS THEN
       DBMS_OUTPUT.PUT_LINE ('OTHER EXCEPTION ' ||SQLERRM);
END;

Open in new window

0
 

Author Comment

by:cesarchavez
ID: 38803893
im not sure how that helps me.  I cant change the UI side.  so the person doing the inserts shouldnt know any different.  All he knows is that when he tries to insert duplicate event type A it works and when he inserts duplicate event type B it doesnt.  So i must allow duplicates into the database...i cant even create the unique constraint at this stage.
0
 
LVL 14

Expert Comment

by:ajexpert
ID: 38803907
can you provide example (data) of duplicate event type A that works, and event type B that doesnt? and what error is raised
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:cesarchavez
ID: 38803910
i just want to contrain my data to support this requirement.

so. everything in the table below should be able to be inserted without error

eventid, type, time, description
1, A, 1, ok
2, A, 1, ok
3, A, 2, ok
4, B, 1, ok

but now when i try and inser

5, B, 1, ok

it should fail because my table is allowed duplicate entries of A but not of B.  Hope that helps
0
 
LVL 14

Expert Comment

by:ajexpert
ID: 38803936
Don't think Oracle has any constraint supporting your business case. May be other experts can comment
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38805359
If you can't change the UI then you have to add code to the database.

I also don't think Oracle has this type of a constraint out of the box.  I beleive you will need to handle this in code with a trigger.

I'm not sure if this is a faster trigger but wanted to throw it out there:  two tables, a view and an instead of trigger.

Create a table for event A that allows dupes.  Create a table for event B with the unique constraint.  Create a view that is a union all against both tables.

You can then create an instead of trigger that checks the value for the event and inserts into the correct table.  Then the unique constraint on table B should handle the dupes.

It might be faster than a normal trigger.
0
 

Author Comment

by:cesarchavez
ID: 38807888
not sure what to do here where no solution is available.... do i close the question or award points?
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38808196
Leave it open for a while.  Some of our Experts are in different time zones and are busy.  Some of them may not have had a chance to see this.

Also,  we provided the answer you asked for.  Just not the one you were looking for.

Did you check the performance of instead of triggers over a normal trigger?

Check out:
http://www.experts-exchange.com/help/viewHelpPage.jsp?helpPageID=23

"You can't do that"
Sometimes, you will get an answer that isn't what you want to read, but it still may be the correct answer, and you should award points to the Expert that gave you that information.
0
 
LVL 38

Accepted Solution

by:
Geert Gruwez earned 500 total points
ID: 38808793
is the data parent to other records ?
or part of a bigger transaction ?

if not, set up a job which runs every so often to delete the offending records
 this is in background, no impact on any front end

i admit it will look rather odd to the frontend user seeing his records disappearing after some time.
0
 

Author Closing Comment

by:cesarchavez
ID: 38810726
I didn't think of this.  Great idea and is will work in my scenario.
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Suggested Courses

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question