This week's book giveaway is in the Java 8 forum.
We're giving away four copies of Java 8 in Action and have Raoul-Gabriel Urma, Mario Fusco, and Alan Mycroft on-line!
See this thread for details.
The moose likes JDBC and the fly likes Constraint with subquery Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Java 8 in Action this week in the Java 8 forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Constraint with subquery" Watch "Constraint with subquery" New topic
Author

Constraint with subquery

Ben Flowers
Greenhorn

Joined: Sep 12, 2011
Posts: 17
Hi Basically in my DB i have a police_officer and appointment table
in the police officer table there may be many ranks, but in the appointment table there can only be sergeants.
I.e. to make an appointment the police officer must be a sergeant.


when i put a constrain of


(rank_id = 2 = sergeant)
This doesnt work as subqueries are not allowed, ive tried to write a trigger but cant seem to get it working. Does know a good way to get around this?
Thanks,
Ben
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3454
    
  47

This depends on the database you're using (and possibly its version), which you didn't specify.

On Oracle 11g, probably the most elegant solution would be to define a virtual column RANK_ID with a value of 2 in the APPOINTMENT table and create foreign constrain on POLICE_ID and RANK_ID columns from the APPOINTMENT to the POLICE_OFFICER table. A unique constraint over these columns in POLICE_OFFICER table must be defined for that.

If the virtual column is not available or cannot be constrained in your database, you could create real RANK_ID column and populate it either through a trigger, or a DEFAULT clause. Then define the foreign key constraint. I'd suggest adding a check constraint that would fix the column at the desired value (2).

This will constraint the APPOINTMENT table to refer only those rows that have the desired RANK_ID. Furthermore, it will prevent changes in the POLICE_OFFICER table that would break this constraint, which is probably an important part of the design. If you actually are on Oracle, don't forget to index the foreign key (even if using the virtual column).

Trying to do a trigger-based solution requires intimate knowledge of the database locking and concurrency model. In Oracle you'd need to properly lock the parent table during the check and also have another trigger that would prevent demoting an officer from his Sergeant rank if he has active appointments, again with proper locking. Remember that other databases might have other caveats.

Declarative constraints are safer, as you cannot screw the concurrency model, and will probably perform and scale better, as they are coded natively in the database. Plus, in Oracle, they can be deferred as any other declarative constraint if need be.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Constraint with subquery
 
Similar Threads
Watch out for little old ladies joke
SCJP centres in bangalore
Stupid Jokes
Java BlueJ
Only fun forwards for fun ...