CODEWITHSUNDEEP
mysqlnestjstypeorm
Phin
Phin

Reputation: 68

TypeORM: duplicate entry error after changing length of primary key

I am making a table for an email with Nestjs, MySQL, and Typeorm. I have char type primary key length of 39, and have put a unique string made up of UUID concatenate with some extra sting in it. Here is the way I make the primary key with Typeorm in Nestjs entity file named 'email'. There is no @PrimaryColumn or @PrimaryGeneratedColumn in it.

@Column({
  primary: true,
  type: 'char',
  length: 39,
})
id: string;

There were some data in the table. After I changed the length from 39 to 45 and run the server, an error happened at TypeOrmModule. I didn't try to save anything. It's strange that I have seen similar errors during inserting data thru googling.

QueryFailedError: Duplicate entry '' for key 'email.PRIMARY'

Even I set back the length to 39, still the error happens. The only way that I've found to run the server is by dropping the table and make a new one.

I have 2 questions about this error.

  1. Is there a solution to change the length of the primary key, keep the data in the table, and run the server?
  2. How '' - an empty string from the error sentence - affects the uniqueness of the primary key?

Upvotes: 1

Views: 6075

Answers (1)

Hamid Sajjadi
Hamid Sajjadi

Reputation: 133

My guess is that you have synchronize: true in your Typeorm config object. And based on Typeorm documents

Indicates if database schema should be auto created on every application launch. Be careful with this option and don't use this in production - otherwise you can lose production data.

So what actually happens is that when you change the length of a column and launch your application typeorm tries to sync this change and update your schema. And for the reasons mentioned here, Typeorm does that by dropping the column entirely and creating a new one, where it encounters a duplicate error.

  1. For your first question, you can use migrations to have more control over how the schema is updated. Though I should mention that in migration typeorm behave similarly and drops the column for the change, but at least you can edit the migration file yourself to ensure it does what you want it to do. Or you can edit the schema of database directly. Whatever you choose to do, synchronize probably is never a good idea, except for the very early stages of development.

  2. As your second question, since your primary column is a char with no default value, the new column values will be filled with '' noramally, as default of char for fields that are not nullable; but obviously it encounters a duplicate error on a primary column where uniqueness is a most too; hence the error Duplicate entry '' for key 'email.PRIMARY'

Upvotes: 5

Related Questions