02-24-09, 17:27 #1Registered User
- Join Date
- Feb 2009
Unanswered: How to add db "constraints" that works on relations in oracle10? - A newbie question
Let's say I have the following tables: A group table and a users table, that are connected by a secondary table in a many to many relation.
How can I make sure, in the database level (not in the application level) that when inserting or updating rows in one or more of those tables, that there won't be two groups with exactly the same users.
Meaning that the users are my unique identifier to a group.
The reason I'm asking it, is that I know that it's possible to put a unique constraint on a column or a tuple of columns, but I don't know if it's possible to put it on a "relation".
It can get even more elaborated. e.g. I can say that I don't want two groups in the database that has the same users and also that the value of their "type" column is the same.
The reason I am not rushing into doing it in the application level is that it is annoying and full of race conditions.
Say I want to create a new "group" with several users and return it, while ensuring that I won't insert an existing group - but rather returning it if it already exist.
The only solution I can think of in the application level is:
1. Query for existing group in the according to the users that I want for the new group.
2. Create a new one if the group didn't exist.
But it is still not a good solution, because what if there are two people working with my application at the same time. The two want to create a new group with the same users in a relatively close time.
One may check if the group already exists, find out that it doesn't exist and create a new group, but even before creating the group and commiting, it's possible that the other user also got to the same point, went through the querying part and found out that the group doesn't exist, and his now creating it as well.
So then the two groups, which are exactly the same would be inserted into the db (and no constraint will prevent it). Of course I can refine my earlier suggestion and write:
1. Lock the secondary table for read and write.
2. Query for existing group in the according to the users that I want for the new group.
3. Create the new group, only if it doesn't exist.
4. Commit and release the lock.
But as you see, it's quite complicated. That's why I think I want it in the db level.
02-24-09, 18:12 #2Registered User
Provided Answers: 1
- Join Date
- Aug 2003
- Where the Surf Meets the Turf @Del Mar, CA
Post DDL for tables.
Post DML for test data.
Post expected/desired results.
>A group table and a users table, that are connected by a secondary table in a many to many relation.
Perhaps this "design" is why you find this problem to be so challenging.
Please explain in as much detail as needed (by presenting actual tables) why you believe the "secondary" table is required.
Design the application to Third Normal Form.
Last edited by anacedent; 02-24-09 at 20:00.You can lead some folks to knowledge, but you can not make them think.
The average person thinks he's above average!
For most folks, they don't know, what they don't know.
Good judgement comes from experience. Experience comes from bad judgement.