0

So I have a Customer table that is linked to different tables. For instance, a Configuration table is linked to Customer table.

Instead of using Customer Id as foreign key to the Configuration table, I would like to introduce a secondary id called Reference. This Reference has a value similar to GUID and would like to use this as Foreign Key to the Configuration table. The reason behind this is because I don't want to use the Id of the Customer table. We use integer as a data type for this and I don't want to expose this.

What could be the possible complications on implementing this approach?

5
  • 1
    How is exposing surrogate key #1 any less secure than exposing surrogate key #2? There is also no reason you need to expose this to any users regardless of what pk/fk is being used. Commented Aug 10, 2020 at 13:56
  • @LowlyDBA well we show this via URL if a user navigate to a configuration page and want to edit their configuration, smilarly with other that is related to a customer. So instead of showing the intergervalue as part of URL we want to show a different one
    – rpmansion
    Commented Aug 10, 2020 at 14:05
  • @LowlyDBA as integer is easy to guess than than guid. I can change the id to guid but do not want this as it is not good for indexing.
    – rpmansion
    Commented Aug 10, 2020 at 14:07
  • As long as reference is unique in the parent table, you can reference it in a child table Commented Aug 10, 2020 at 14:12
  • 1
    Security through obscurity isn't valid; proper control mechanisms should handle if anyone guesses an entity's ID or not. I'd recommend against doing this given your motivations. Commented Aug 10, 2020 at 15:40

2 Answers 2

0

It's not complicated to do. You need to create the Reference ID as an alternate key in the Customer table with a unique constraint. Then you can use Reference ID as a foreign key to the Configuration table. I generally try to avoid this however as it can get confusing and harder to maintain the data model.

0

I would make the customer ID column be a unique constraint and make the other column be the primary key. One of the database design philosophies is to use non-data columns to enforce referential integrity and to never use them in at the application level.

Some of benefits is that you can use an autogenerated sequence for the PK column (to control how they are generated) and restrict update access on user roles to that column (prevent buffoonery by applications).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.