Search code examples
sqlpostgresqlintegerconstraintscreate-table

How to limit data type to 6 digit numbers in SQL?

I have a table in sql for a bank account

CREATE TABLE (
    name VARCHAR(100),
    bsb INTEGER,
    account_num INTEGER,
    PRIMARY KEY (bsb, account_num)
);

How do I put a constraint that will limit BSB to only integers that have 6 digits including 000,000 all the way to 999,999? Do I need a constraint or to use a different data type?

I know I can do this check restraint to limit the size of an int to be 6 digits CONSTRAINT Bank_BSB_CHK CHECK (BSB < 999999) but a BSB can start with 0's but just has to be 6 digits long.

Solution

  • Since the data is a sequence of 6 digits with no mathematical meaning it is better to use a CHAR(6) data type and put a constraint on the pattern the bsb can take. This would be the correct constraint to limit the pattern to 6 digits.

    CONSTRAINT Bank_Account_BSB_CHK CHECK (bsb SIMILAR TO '[[:digit:]]{6}')