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

Question

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.

Answer 1

• 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.

Answer 2

Your mistake is you want to use the Name instead of ID because Name is more Readable. What you should doing is making your ID more readable.

Think of it as RoleCode instead of RoleID

Role(RoleCode Pk, Name, Other_Columns)
Command(CommandID PK, RoleCode FK, Other_Columns)

Sample Data

RoleCode      Name              Other_Columns
--------      ----              -------------
Admin         Administrator
PwrUser       Power User
User          Standard User

Now your referential integrity is safe!

Answer 3

YES, it is ok to use a non-ID column for foreign key reference unless and until it MUST be a column that has a unique constraint on it.

The basic definition of foreign key is : "Primary key in one table acting as a Foreign key in another table". By standard SQL, the reference of a foreign key should be the PRIMARY KEY or a UNIQUE KEY in the referenced table.

If you can non-ID column as your primary keys, then that's good. However, there are some advantages of using ID column as a foreign key reference :

1. ID columns, especially those that are IDENTITYcolumns are also good as indexes (sometimes) in a sense that they almost never get updated, and if you don't delete rows from the table, you decrease index fragmentation.

2. When you don't have a create_date/entry_date column and you ever need to check data in the order they were entered.. having an ID column as an identity makes that possible.

3. While compound keys work, a single primary key can be sometimes be easier to work with. For instance, on doing deletes it is very easy to single out a particular row. It is also often more efficient to search on a numeric key.

Thank you !