1

I am very new to database concepts and currently learning how to design a database. I have a table with below columns...

this is in mysql:

1. Names - text - unique but might change in future
2. Result - varchar - not unique
3. issues_id - int - not unique
4. comments - text - not unique
5. level - varchar - not unique
6. functionality - varchar - not unique

I cannot choose any of the above columns as primary keys as they might change in future. So i created a Auto-Increment id as names_id. I also have a GUI( a JTable) that shows this table and user updates Result,issues_id and comments based on the Names.Names here is a big text column. I cannot display names_id in the GUI as it does not make any sense in the GUI. Now when the user updates the database after giving inputs for column2,3,4 in the GUI i used the below query to update the database, i couldnt use names_id in where clause as the Jtable's row_id does not match with the names_id because not all the rows are loaded onto JTable. 

update <tablename> set Result=<value>,issues_id=<value>,comments=<value> 
where Names=<value>;

I could get the database updated but i want to know if its ok to update the database without even using the PK. how efficient is this? what purpose does the surrogate key serve here?

Matt Busche
  • 14,216
  • 5
  • 36
  • 61
Raju
  • 53
  • 5

1 Answers1

1

It is perfectly acceptable to update the database using a where condition that doesn't reference the primary key.

You may want to learn about indexes and constraints, though. You query could end up updating more than one row, if multiple rows have the same name. If you want to ensure that they are unique, then you can create a unique constraint on the column.

A primary key always creates an index on that column. This index makes access fast. If there is no index on name, then the update will need to scan the entire table to look at all names. You can make this faster by building an index on the field.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786