How to make database changes without having a clear primary key?

Asked

Viewed 747 times

2

I need a method to change data in an SQL Server table.

According to my client, all the table data can be changed. For having this possibility, I am without a fixed and unique reference frame for the bank to know which data will be changed.

UPDATE tabela SET campo = 1 
WHERE outro_campo = 5

My client suggested the creation of a screen code, which would be invisible to the user, for the bank to use as a change reference, but being invisible, the user has no way to enter it, and to display this code when the screen is opened, cannot, because there is no data in the fields to use a SELECT.

sql database sql-server vb.net

  • Catharina, the ID as PK, you know it’s unique to be auto_increment. You have to have a screen on your system to enter the data, but do not need to enter the ID. But if you want to show a field on the screen to show the registration ID that will be registered, just do one SELECT MAX(ID) FROM TABELA and sum + 1 to show in the field. The MAX will get the last registered ID. Then to make the change of some record, just make a SELECT usando o WHERE = ? ID and return the values of the fields on the change screen.

  • Did any of the answers solve your problem? Do you think you can accept one of them? If you haven’t already, see [tour] how to do this. You would help the community by identifying the best solution for you. You can only accept one of them, but you can vote for anything on the entire site.

2 answers

2

A basic technique of creating tables in a database is to have a column ID as the primary key, so the data is free to be worked as you want. This identifier which will obviously be unique and auto-incremented is necessary for the application but not for the user. In general the user does not even know it exists, it is something absolutely internal. Example:

ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY

I put in the Github for future reference.

  • Yes, all my tables have a PK, and I also know their concept... But how can I point this out in code? If it is something internal, or as I usually call "just to organize the data", and the user does not even know it exists, what reference would I have on my screens to show the bank what should be changed? That is my problem.

  • 3

    Why do you need it on screen? You need it in code. There is no difference in code, just don’t show it on screen.

  • I am doing the program in VB.NET. The data will be changed from what will be inserted in text boxes. The ideal would be that the user put a value known to him, which was unique and could not be changed, to make the change. That value would go on condition 'WHERE'.

  • You said that information doesn’t exist. I wouldn’t do it like this, there’s no need to do it, but if you want to do it like this then do it like you described. You don’t need any of this, I’m voting to close the question because it seems like it has nothing to do with what was actually asked. You don’t have enough information to answer.

  • @Catharinalopes, I don’t understand very well what you know about PK. In the quote The ideal would be that the user put a value known to him, which was unique and could not be changed, to make the change. That value would go on condition 'WHERE', This violates the conditions of PK , A PK is immutable, invariable, ie can not be ALTERADA. You just need to make your Selet * from tabela where id = 1; and do the Update tabela set campo1 = '', campo2 = '' where id = 1;.

0

The ID is for internal control of your code with the bank... vc n need to pass this or something to the user.

If he is going to change something of the bank, he needs to know what he wants to change, your code and bank will know what is to change by Id.

  • But it is not direct in the database, it is from the program. I exemplified in the text of the post, with the UPDATE code.

  • example: UPDATE table SET column(s) (column 1, column 2) VALUES ('VALUES', 'VALUES') WHERE ID(OR OTHER KNOWN COLUMN FOR SEARCH) ='ID_DA_SUA_TABELA or VALOR_CONHECIDO_DA_PESQUISA '

  • of course the ideal n is you go straight to the update.. make a path for it: he first looks for what he wants to change > alter so you make a SELECT with the column q he knows > he chooses what he wants to change > you take the ID of what he wants to change and in UPDATE you use the ID... This prevents you to change the wrong value... let’s say he wants to change the number of the little Joe, if you have 2 little jewels, clay... with id this does not happen

  • Of course not, but I’m not having problems with INSERT or DELETE.

Browser other questions tagged sql database sql-server vb.net

You are not signed in. Login or sign up in order to post.