Help with sql statement

Question

I want to guarantee that a token is assigned to an order. All available tokens are inside the Token table and each token can ONLY be assigned to 1 order. Here is the sql I came up with:

SET XACT_ABORT ON;
BEGIN TRANSACTION

-- Reserve token.
SELECT @token = token, @tokenId = id FROM Tokens WITH (UPDLOCK) WHERE taken = 0;
-- Take token.
UPDATE Tokens SET taken = 1 WHERE id = @tokenId;
-- Assign token to the order.
UPDATE Orders SET token = @token WHERE ID = @orderId;

COMMIT TRANSACTION

Can the above code guarantee that

1. if there is an available token inside Tokens table, then it will be selected and assigned to an order.
2. each token will not be assigned to more than 1 order.
3. Statement will never fail in error.

Do you see any other potential problems with this statement?

Answer 1

Instead of aenter code here "taken" attribute, why not just put a nullable OrderId in the token table. This accomplishes yr objectives with just one column attribute.

Update Tokens SET 
  Orderid = @orderId  
Where tokenId = (Select Min(TokenId)
                 From Tokens
                 Where Orderid is Null)

EDIT: Added schema to illustrate one to zero or one relationship

Tokens
TokenId integer non-null PK
... other attributes
enter code here


Orders
OrderId Integer Non-Null PK, FK to Tokens.TokenId
... other attributes

Now, all you need to do is use an available tokenId from the tokens table when you create the new Orders record.

Insert Orders (OrderId, [Other attributes])
Select Min(TokenId), [Other values]
From Tokens t
Where Not Exists
  (Select * From Orders
   Where OrderId = t.TokenId)