Is it not a good practice to display primary key on UI?

Still, I think the device should have some unique identifier, other than the ID generated using the sequence. It should have some kind of serial number or so... Otherwise, there is the risk of entering the very same device N times and losing it completely.

There are cases when you may want to hide a PK, even if the PK is generated by a sequence. See how close your situation is to these examples. (I don't see, in particular, how such things might apply to id's assigned to devices, not to people or to financial assets.)

1. Personal identification number/code (in the U.S., Social Security Number). At least in some countries, identity theft is a big concern, and the most valuable piece of information for thieves is the PIN (SSN in the U.S.). One might make the case that SSN's are generated by sequence (with a number of rules though). And some databases do use the SSN as primary key. I would not want that PK to appear in the UI, if the UI is to be accessed by users who don't otherwise have access to the PIN/PIC/SSN.

2. Same with bank or brokerage account numbers, mapping to customer id. Right now your users can access information by customer id. They may need to be able to check information from one account (say from the "joint checking account") but they have no business knowing the actual account number. Account numbers, again, are probably generated by sequence. I wouldn't want to expose those numbers to the "general public" (depending on who, exactly, has access to the UI).

3372439 wrote:Hi AllPresently we get device details though a feed where multiple devices can be mapped to single CustomerId. The device details are loaded in table : TBL_DEVICES . The devices presently do not have any natural primary key so we have created a sequence as primary key . We had a new request to assign a unique key for each device so that they can be searched from the UI using the unique key (Presently they can search using CustomerId) . The team has asked me to create a new column and increment using a sequence . TBL_DEVICES ( Existing structure)-----------------DEVICE_ID NUMBER PRIMARY KEY using SequenceCUSTOMER_ID NUMBER FOREIGN KEYDEVICE_DESC VARCHAR2(100) CAPACITY NUMBERCan't we use the existing primary key as a unique key? . When I asked the same they said that showing primary key on UI is not a good practice . I was not convinced as it doesn't make sense to add a column and sequence which adds no value.Please suggest.Thanks

Implicit in using SEQUENCE as PK, is that only a single record will exist for EVERY device.

In other words can the same physical device have data record in more than 1 file of data to be loaded?

If so how do you plan on associating the multiple device records to the same physical device?

I didn't mean "you" using my SSN in your own database (if you are my dentist, say). Rather, I mean the Social Security Administration itself, and the various users who feed into and from their database.

Same with the bank - my bank assigns an account number to me, perhaps by sequence, starting from their database and then flowing from there to everywhere else.

I guess your suggestion, in these cases, is that the SSA and the bank are doing it wrong. They should assign a SSN to me (or an account number), and that number should be unique, but such numbers have no business being the PK in their tables. I would agree with that 100%, but I don't control what the bank and the government are doing, in their IT department. If for whatever reason they DID use these sensitive numbers as PK (as they may have, I don't know), then I wouldn't want them to expose this PK.

I think we are pretty much in agreement, I am perhaps just more paranoid than most.     On the other hand, if such idiotic practices were not present in real life, I wonder how we get such huge data breaches seemingly all the time...

GregV, a new bank account number is normally constructed rather than manually entered on creation so I think account numbers may well be a PK.  I have seen SSN used as the PK in several systems though more modern practice is probably to use a surrogate key instead.

- -

ALL, contrary to what people believe SSN are not unique since somewhere between 10K and 40K were reissued by mistake and the article below indicates there were some other issues related to uniqueness.  The SSN can contain a non-numeric digit usually appended to the end of the number.  Such numbers are generally assigned to non-citizens who inherit benefits.

- -

Article on SSN

https://www.ssa.gov/policy/docs/ssb/v69n2/v69n2p55.html

- -

user3372439, I think BEDE and MathGuy gave you good responses.

- -

HTH -- Mark D Powell --