Why not unique IDs but Strings as Primary Key

Hello there,

I was wondering, why Frappé / ERPNext is not using unique IDs as Primary Keys, but Strings (VARCHARS), which are limited to 140 characters by default (which can be changed to 255 with the customize form feature)

I was always convinced, that data should be stored with a PRIMARY KEY with an autoincrement id. Because of the naming series, it is not necessary to use the sql auto increment feature, but I think it should be an integer.

For example: Often there are customers with the same name. Why would someone want to suffix a counting number to the name of a person? Or products with the same name, or multiple shipping or payment addresses for the the customer, and so on.

Maybe there is a reason for not doing this at all. Maybe it is about how Frappé / ERPNext get things done.

I was also wondering of what would happen, when I decide to change the whole system to use those unique IDs.

I am asking for learning more about Frappé / ERPNext and your decisions.

Thank you in advance.

@ci2016, I can tell to you a sample.

Submittable documents, are versioned using -1, -2 etc according the number of revisions. It is very usefull.

Another case is, you can have distinct ID’s by Company, by Customer, By Territory, things that if you have a serial numeric ID, do you will need replicate it.

I believe that the team, ellected Strings as PK, because the user can customize it as they need, and the naming series is very powerfull, when we mean that the user can customize it.

1 Like

Hello Max,

I don’t really get that point. I know lots of web projects, which are using mysq databases with a unique integer primary key, whichs auto increment is set.

Having an unique integer primary key, for example ID, must not mean the user won’t be able to customize the forms or use the naming series feature.

I am asking further and look forward to understand this and learn about best practices.

I do like the style of frappe using string as a primary key. When you create a new doctype you can use the existing way of assigning primary which is a random unique number or use your own logic assigning primary key. It’s your option. If you want to do that go ahead just use the autoname method and assign your logic for incremental unique key.

I thought maybe it would lead into incompatibility of updates or 3rd party modules, when I change the default behaviour of ERPNext.

@ci2016 The biggest problem not using INT keys is that the index size is much larger

There are lots of benefits too, the biggest being that all foreign keys become human readable and we avoid using joins even for simple queries.

Its a design decision and there are pros and cons both ways, right now we have not faced major issues.


I also did not faced major issues, only that I could not import customers with the same name at the first try. I am using the following code to achieve creating customers with the same name.

name = customer_name
suffix = 1

while frappe.db.exists("Customer", name):
	name = customer_name + " - " + str(suffix)
	suffix = suffix + 1

Maybe it’s useful for someone who is facing the same problem.

1 Like

@ci2016, usually, I use “tax_id” for customer naming instead of “Customer Name”, or I use the “Naming Series”

If you know that you will have conflicts using the customer name, use another field, or make the use of the naming series!


@ci2016 about the case of ERPNext I also like the way they use there naming the primary key as @rmehta as said its more easy to read and you do not always make query just getting the name of the foreign key. If you using the autoincrement you have to to make a query just getting name of the foreign field. If you want to change ERPNext the way you want its hard to keep up the changes of the core team.

@rmehta for me its ok to have a bigger size of index. Hard disk Space now is cheap :smile:


I was reviewing frappe a few days back, and liked it a lot. But this particular design turns me off and has led me to not use frappe yet.

In my brief tests, I have the following concerns:

  • Updating company name will require all related tables to be updated as well.
  • Renaming a customer will update the customer_name field, leaves customer.name intact. However some fields still display value from customer.name.

Note: field names are from the top of my head. please correct me if i’m wrong.

1 Like

I have a similar issue with Territory doctype. I’ve a situation where I have duplicate town names, but the system won’t allow that.

I want to do a similar thing to this solution where if the new territory name being persisted exists and it is a group, then add say “A” to the name and if it is not a group add say “B”.

Looking at the frappe api and erpnext code, I am a little stumped as to where to find the code that is responsible for persisting territories. Any heads will be appreciated.


you may have to check out frappe hooks, which you can find here:

I would create a hook for the Territory doctype, either validate or before_save, maybe on_submit or before_submit (just try whats working best). Pass the {dotted.path.to.function} to a function which checks the availability of the Territory name. If its not available append your suffix and try returning the changed name of the territory. I assume Frappe / ERPNext will use the returned string.

Use my code above:

while frappe.db.exists("Customer", name):

This is the only option to make it really work, when you have 3 times the same territory name.

if, i have random int id for current employee and i have to make unique also, so what i do