There's situation when I have table named "status", which only contains ID (PK) and name (string, unique). Also, other table has reference to this status (e.g. status_id)
let's say, there's two statuses:
1 - status1
2 - status2
Now, I'd like to insert/update record in table2 (which has reference to status table). What is the best way to do it, should I hardcode ID of status that I'd like to set or should I query by name, then get ID and assign it after?
NOTES: this is also general programming question (no direct SQL queries). I was not able to find a tag for it.
If name is a column including unique values, you can use name field to get the ID and then use the ID. However, Usually the name column does not include unique values, so it is required to use other columns to receive only 1 id instead of multiple.
See the situation:
ID name
1 John White
2 John White
Here, if you use name field, you will get 2 different IDs returned which will cause an error. That's why you'll need another approach like:
..
where name = @name and dateOfBirth = @dob and MothersName=@mothersname
to make sure one unique id is being returned.
To sum up, if you are sure the name field includes unique values, use the field to get the ID instead of using the ID value hardcoded. Otherwise, you can try to create a key in the config file like "lookupid" and use its value instead of using the ID hardcoded still, will be better to maintain for the future.