title: Transferring "Money" between accountID in MySQL
body: <p>I have a question which I have tried to Google but haven't find an answer to yet. What I am trying to do is transferring money between two accounts in MySQL with a stored procedure.  If I for example use Call transfer (20,'Test',3,5). Then I will transfer 20 dollars from accountID 3 to accountID 5 and write the message "Test" , which is what it should do. </p>
  
  <p>At the moment it is however possible to send money between non-existing accounts and the amount can be &lt;0 for the sending accountID, for example call transfer (20000000,'Test',34,54). </p>
  
  <p>Does anyone know how I can solve these problems(amount has to be >=0 and only existing accountIDs can receive and send money)? The code I am using can be found below:</p>
  
  <p>Creating the tables:</p>
  
  <pre><code>Create table Account 
  (
  AccountID int AUTO_INCREMENT primary key 
  ,amount decimal check (amount &gt;= 0));
  
  Create table Transfers 
  (
  TransfersID int AUTO_INCREMENT primary key
  ,amount decimal
  ,from_account_id int not null
  ,to_account_id int not null
  ,note varchar(50) not null
  ,datetime datetime not null
  ,Constraint from_account_key foreign key (from_account_id) references 
  Accounts (id)
  ,Constraint to_account_key foreign key (to_account_id) references Accounts 
  (id)
  );
  
  insert into accounts (id, amount) values (1, (rand()*100));
  insert into accounts (id, amount) values (2, (rand()*100));
  </code></pre>
  
  <p>Creating the stored procedure:</p>
  
  <pre><code>delimiter //
  create procedure transfer (amount int, note varchar(50), sending_account 
  int, receiving_account int)
  begin
  
  start transaction;
  update accounts as A
  set A.amount = A.amount - amount
  where A.AccountID = sending_account;
  
  update accounts as A
  set A.amount = A.amount + amount
  where A.AccountID = receiving_account;
  
  insert into transfers values 
  (TransfersID, amount, sending_account, receiving_account, note, now());
  commit work;
  
  end //
  delimiter ;
  </code></pre>