Check for field values to be unique when storing into the DB
When you use fields GUI to add fields to a content type, it is correct that the GUI do not let you set the UNIQUE constraint.

However, the fields created in this way is stored in the SQL database. They're in a table named field_data_ followed by the machine name of the field.  So it is possible to add the UNIQUE constraint to any field after it has been created by means of an SQL ALTER TABLE statement, e.g.:

ALTER TABLE `field_data_field_user` ADD UNIQUE (
  `field_user_value`
)


As long as you only do this to fields that you've created yourself, I do not see any problems.

However, it is also possible to create the content type yourself as an entity, using the Drupal Schema API.  If you've business logic that isn't part of the fields repertoire (such as deletion of an old research assignment before inserting a new one), it is probably better to use the Schema API istead of fields to create the content type.  This is still not plain SQL (the Schema API expects you to declare the database tables in a structured array, and Drupal DB API is using PDO-like objects to interact with the database), but it has the same power and flexibility as plain SQL.

Note that declaring the user and project fields unique will not get the old assignment automatically deleted before the new is inserted.  All this constraint does is to reject operations that does not satisfy the contraint.

To use transactions in Drupal, you just call $txn = db_transaction(); in your own module. The transaction will remain open for as long as the variable $txn remains in scope. When $txn is destroyed, the transaction will be committed.