I have an account table which is laid out in 3NF:

CREATE TABLE account (
    account_id INT AUTO_INCREMENT,
    customer_id INT NOT NULL,
    account_type_id VARCHAR(15) NOT NULL,
    opening_balance DECIMAL(10,2) NOT NULL DEFAULT 51,
    account_number INT(8) NOT NULL,
    sort_code VARCHAR(8) NOT NULL,
    created_date DATE DEFAULT now(),
    branch_id INT NOT NULL,
    PRIMARY KEY (account_id),
    UNIQUE(sort_code, account_number)

However, I want to know if there is a way to create a trigger that upon inserting into this table a unique sort_code and account_number can be generated. The account number should just be a unique 8 digit, however, the first 3 digits will contain the id of branch_id from a separate branch table, e.g an account belonging to branch_id 700 should have an account number starting with 700. And sort code should have this format "XX-XX-XX" where the X's numbers and this should also be checked to be unique. Is it possible to make a trigger to do this in MySQL/mariadb?

  • 2
    It is certainly possible. What's your real question?
    – mustaccio
    Dec 12, 2021 at 22:33
  • Of course this everything is possible. But the task itself is problematic - in theory you may reach the state when there is no value that matches your conditions and is not present in the table already...
    – Akina
    Dec 13, 2021 at 5:14
  • @mustaccio what do you mean,ive asked my question above how to geenrate a unique account number and sort code, in the format mentioned above, within a transaction, after inserting into the account table.
    – Mj _
    Dec 13, 2021 at 8:46
  • Assuming this is for the UK, the bank and branch are encoded in the sort code, not the account number. See e.g. the explanation on Wikipedia.
    – dbdemon
    Dec 13, 2021 at 11:05
  • @dbdemon so is the account number is a random unqiue 8 digit code?
    – Mj _
    Dec 13, 2021 at 11:09

1 Answer 1


There are probably more than one way to do this, but here is one idea.

You don't have to use triggers.

Use MariaDB sequences to generate unique numbers - assuming it's OK that the numbers are created sequentially.

Assuming you are designing a system for one particular bank, and the digits in the sort code are used to identify the bank and branch, then:

  • The first two digits of the sort code identifies the bank and will always be the same, so you don't need to store these.

  • Put the branch digits in the same column as the account number digits.

  • Create one sequence per branch where you specify a unique range for each, defined by the sequence's MINVALUE and MAXVALUE.

  • For display purposes, you can decode into sort code and account number by using generated columns.

Basic example code for sequences

CREATE SEQUENCE seq_branch_1 
  MINVALUE 100000000 
  MAXVALUE 199999999 
  START WITH 100000000 

CREATE SEQUENCE seq_branch_2
  MINVALUE 200000000 
  MAXVALUE 299999999 
  START WITH 200000000 

And so on.

At the time of creating an account you presumably know which branch it's for, so:

INSERT INTO account(customer_id, account_type_id, ... unique_account_number, ...)
  VALUES (416, 2, ..., NEXTVAL(seq_branch_2), ...);
  • Thank you, can you give some brief template code for this so I can get a better understanding of what you mean.
    – Mj _
    Dec 13, 2021 at 11:57
  • @Mj_ does that help?
    – dbdemon
    Dec 13, 2021 at 12:26
  • thank you! I last question regarding the sort code , so did you say sort code first pair should be the bank code, then the 2nd 2 pairs are the branch id?
    – Mj _
    Dec 13, 2021 at 12:34
  • @Mj_ yes, that is my understanding. Although (speculating here) it's possible this varies between banks. E.g. an Internet-only bank with no physical branches might want to use all those digits to identify the account instead. That said, who needs that many digits? I don't know.
    – dbdemon
    Dec 13, 2021 at 12:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.