7

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.

TechCrunch
  • 643

3 Answers3

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

Tulains Córdova
  • 39,449
  • I guess I am totally fine with using RoleName for primary key as well. Just thinking about cases when I ever rename the role.. – TechCrunch Jul 12 '16 at 23:21
  • 3
    Don't want to start a holy war, but it should be noted that this is also an advantage of surrogates: If you use business keys everywhere, and then your business requirements change (e.g. now we want to allow duplicates), you have to do an expensive multi-table schema change instead of a cheap constraint change. – Kevin Jul 13 '16 at 06:12
  • +1, but also bear in mind there are good advantages for using the ID field (see, for example, DocBrown's answer to this question), so you need to weigh up the pros and cons of each approach. – Jules Jul 13 '16 at 07:58
7

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!

Morons
  • 14,704
  • 4
    But when "Power User" needs to be changed to "Expert", would you really keep the RoleCode PwrUser? – RemcoGerlich Jul 13 '16 at 09:35
  • 1
    @RemcoGerlich: That's why a "Role" should have something like a "Name" or "DisplayText". The PK could be PowerUser, and then just change the Name or DisplayText to "Expert". – Greg Burghardt Jul 13 '16 at 17:11
2

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 !

Purvi Barot
  • 139