Check for unique key violation
I am writing a program where I have mapped a generalization into two tables (actually there are more, but I will leave them out because the principle is the same): Person and Employee. Person stores the general information such as social number (unique) and Employee stores information specific to employees, such as username/password. Both tables have an ID. These two IDs will match, so that an employee with ID 7 in the Employee table will have its general person information in the Person table with ID 7 as well.
Before inserting a new employee, I want to check if a person with a given social number is already registered in the table. Given that the social number column is unique, it throws an SQLException in my face. I can catch that without a problem, but what I want is to display what happened to the user. I can't really do that by catching the SQLException because it's so general.
I figure I could go ahead and use the IF (NOT EXISTS (my_insert_query)), but that would still leave me without a message to display. I could go ahead and execute a select query before my insert where I check for a row with a matching social number, but it seems inefficient to me.
The code below is not exactly what I have, but I will just provide an example query:
How would I go about inserting values in a table if no row with a given social number exists - and displaying a message to the user if it does? From DBLayer to ControlLayer to GUI. I hope you guys understand my problem.
PreparedStatement prep = myConnection.prepareStatement("INSERT INTO Person (id, firstName, lastName, socialNumber) VALUES (?, ?, ?, ?)");
prep.setString("social number"); // Not sure of the English formats for this :)
prep.executeUpdate(); // Couldn't get it to work with executeQuery()
Thanks in advance!