Magento: Add a custom State / Province / Region to a Country

If you need to add a custom State / Province / Region to a Country in Magento, you’ll need to modify the database manually as there currently isn’t a means to do this from within the Magento admin. Fortunately it’s pretty simple – there are 2 tables involved: directory_country_region and directory_country_region_name.

Adding a record to directory_country_region for a specified country_id will make that new entry show up in the State/Province drop box on the address forms. It will also make that a required field, so you need to make sure you add all the possible options.

You then need to add a corresponding record to directory_country_region_name, using the region_id generated when you inserted into directory_country_region. This entry will make the new region show up when a whole address is displayed on the screen or email, e.g. in an order summary.

Example: Add Tamil Nadu to India

So let’s say that like me, you live in India and want to add 2 regions: Tamil Nadu and Kerala. The country id for India is IN, the region code is a unique identifier so I’m going with Tamil Nadu and at the moment I’m only interested in the en_US locale.

First I will insert Tamil Nadu into directory_country_region as follows:

INSERT INTO `directory_country_region` 
(`region_id`,`country_id`,`code`,`default_name`) 
VALUES (NULL,'IN','TAMIL','Tamil Nadu');

Note the NULL entry for the region_id field which will auto_increment. I need to find out this new region_id for my next insert so I can either browse the table manually, or this query will do the trick:

SELECT * FROM `directory_country_region` 
WHERE `country_id`='IN' AND`code`='TAMIL' AND `default_name`='Tamil Nadu';

In my case, the new region_id is 485, so with that I’ll now insert into directory_country_region_name as follows:

INSERT INTO `directory_country_region_name` 
(`locale`,`region_id`,`name`) 
VALUES ('en_US','485','Tamil Nadu');

Now I just repeat those steps for Kerala and I’m all set.