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?
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.