TEXT column as key for a table when there are other tables referencing that table?

Question

I have a UNIQUE TEXT column (“name”) in SQLite for one of my tables (let’s call it “table A”) and i’m considering using that column as the key for the table

The reason i can see for continue using a standard “rowid column” (integer with autoincrement) is that the name could change and in these cases i will have to update the tables which holds relations to table A

And the reason for using the TEXT column as key is that it takes less space, and makes things more elegant in the database (though less elegant in my Python code since i would need to make additional updates)

Are there other things to consider? What choice would you make and would it depend on the number of other tables referencing table A?

Answer 1

The reason i can see for continue using a standard “rowid column” (integer with autoincrement) is that the name could change and in these cases i will have to update the tables which holds relations to table A

This is the best reason not to do what you intend.

The mess that you can get yourself into having to update foreign keys in other tables because the id have changed is simply not worth it. Imagine a user trying to alter the name and it fails because you have forgotten to add the latest table with a foreign key to the list of tables you need to alter.