Check for field values to be unique when storing into the DB I have a content type "Research assignment" which has several fields including the field "field_user" and "field_project".

I want to remove any old research assignments having the same user and project fields as newly stored research assignment, before storing a new assignment in the DB.

This could be happily implemented by an UNIQUE SQL constraint, but it seems that the Drupal way is to use fields, not direct access to DB. What could you advise: to do this with fields or to revert to plain SQL?

Also, could you show how to use transactions in this case? I don't want anyone to see a research assignment deleted just before it is inserted again with probably other fields.
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.