Database – Is it okay to use a non-ID column for foreign key reference

databasedatabase-designrelational-database

I have my tables as below

Role(Role ID, Name, Other_Columns)
Command(Command ID, Name, Other_Columns)

I have association table RoleCommand. Is it okay to have RoleCommand(RoleName, CommandName) instead of RoleCommand(Role ID, Command ID). I feel like it is easier to read or populate the association table in this case if the names are used for reference. Please note, RoleCommand is populated manually with insert queries. There is no Web UI. I will setup unique key constraints and foreign key references on Name columns. Does anyone use this kind of references in production ? Most of the times I see only IDs being used for association tables.

UPDATE:

I am considering using name for foreign key references because the insert queries for RoleCommand are so ugly with nested SELECT queries inside VALUES to get the ID values from names. I don't want to just use IDs in the insert queries, to avoid errors.

Best Answer

  • Using names as FKs is unorthodox at the least. Usually FKs point to the FK of the parent table.
  • You might as well make name the PK of both tables and get rid of the ID altogether
  • You've found one of the disadvantages of using surrogate PKs where good business keys exist. You should be able to read and understand the association table. After all the data doesn't belong to the application, it belongs to the organization (taken from another answer).

My recommendation is that if you've already decided to use surrogates, stick to them and point the FKs to them.

Also; if you created unique constraints on the names columns they are considered key columns so in theory you can point the FK to them but I find it inelegant.

Related Topic