UNIQUE constraint error 19

I set a UNIQUE constraint to the Employee_ID Column (for testing) and I get:

The relevant code is:

Without the constraint, I was able to add as many times the same record as I can (!). That is why I added the constraint :wink:

Ideas ?

Xojo 2021r2.1
Monterey 12.6

Looks like you’re attempting to use the same Employee_ID value for each record, and the database is refusing it.

Does row_DBR specify a unique value for Employee_ID? If not, the Employee_ID column in the database needs to be set as auto-increment.

Or maybe you want the constraint on the employee name column? But this will cause other problems for you if you have more than one employee with the same name.

I set a default Record, then I click in Write Record.
Day ellapsed; then I had the idea to click ma,y times in Write Record and noticed that I am able to store many Records with the same information.

So, I declared Employee_ID as UNIQUE and purposely clicked two times in Write (a new SQLiteDB was created). At the second write, SQLite complains (and I expected that).

I do not set UNIQUE on Family Name nor First Name for obvious reasons…

Employee_ID is the only Column I think I can set to UNIQUE… (beside PK, of course).

And here I am…

You can’t INSERT employee 109 twice, you can UPDATE it many times after the INSERT. And even updating it, you can’t update such ID to an already existing one.

So the unique constraint is working correctly? Giving you an error if you try to add another record with an Employee ID that is the same as another Employee ID already there.

Or are you having a different problem?

Well then you have two choices:

  1. Ensure that you never insert a record with the same id as one in an existing record.

  2. When you get an error, check if the errorNumber is 19, and if so, output a more appropriate error message and give up.

OK: I rephrase my question:

how do I trap that error ? The error appeared in the db.AddRow line… who is located after Try and before Catch error…
(please read the screen shot of the debugger far above before answering)

You’ve already trapped it.

2 Likes

If you are debugging and you are using “break on exceptions” the debugger will stop on that line, but if you continue/resume debugging you will see that your code will move to the catch section.
image

Ah, OK. I stopped on the error line…

So, my fault.

Thanks Alberto.

1 Like

for your own error handling if you not want the ide stop everywhere use in the method
#Pragma BreakOnExceptions false

All answers allows me to change my Try code as:

Try
  App.db.AddRow("Employee", row_DBR)
Catch error As DatabaseException
  If error.ErrorNumber = 19 Then
    MessageBox("A Record with this Employee ID already exists in the Data Base.")
    
  Else
    MessageBox("(AddRow) DB Error: " + error.Message)
  End If
End Try```

and this works fine

Any time I see error checking code like this I think. Why are you letting the user control the Employee ID in the first place. Sure your code needs to use the value, but why would the user have to choose a value.

If a new employee starts I presume they get the next available number. When you open an existing employee their number is displayed and presumably fixed for life.

Can you not make EmployeeID a read only field. Then when you create a new Employee pre-fill the value, or even hide it and inform he user once it is chosen. In other words don’t let them make the mistake in the first place. Let the computer deal with the computer parts and the users deal with the data side.

EmployeeID should be the autoincrement primary key. The database is much better at choosing the next number than you are.

Wrong. This Employee ID is set by the company. At my local McDonald, the numbers are re-used as soon as the max value is reached (probably a 3 digits number).

An Employee Data Base is set by the HR department, and usually, these people knows what they do.

Employee_ID have nothing related (as any of the other Columns excepted the PK) to Data Base inner working.

But, this is my vision of things. Yours may be different.

Case of a Part Number ID: in that case, the value can increase to … I recall some spare parts ID larger than 3,7XX,XXX (at Volvo / Volvo Penta). Auto or ship motors spare parts have to be unique and not re-used since the motor can live for… decades !

Thank you for sharing your ideas.

PPS: None of the other Columns can have a UNIQUE constaint (all of the other fields can be duplicates), only the Employee_ID HAVE to be UNIQUE.

Fair enough. What if admin issues the same number twice? You won’t be able to store it with that constraint in place.

usually, these people knows what they do.

You could have to deal with the unusual.

Dictatorship…

What if admin issues the same number twice?
It is an error, they will correct it and write the new Record.

I may eventually (if someone request it) propose the number of Rows + 1 as the Employee_ID default value, but I will do not talk about it first.
And if this case happens, where to set the highest Employee_ID (for large corporations).

Never give bad ideas to a customer…

Allowing them to choose is a bad idea. :slight_smile:

I have a lot of experience of this.
Just because the company says ‘Emiles employee ID is 1234’
does NOT mean you need to make that the uniqueID in YOUR database.
And indeed you should not.

The INTERNAL personID should be unique, and it should autogenerate.
Then, if you are asked to create a new person, they can be called Emile Schwarz, even if there is another Emile Schwarz in the database already.
The new employee 1234 can be 1234 even if there is another with the same number… this could be re-use of a company EmployeeID (Very common in police forces), and it may be a new period of employment for a previous employee

So the Company EmployeeID is an attribute of the person, just the same as surname and forename.
It should not be the same as the internal PERSON_ID, which must be unique, and which the user will never see.

Whether or not there can be 2 people with the same Company EmployeeID is business logic, not Database restriction.

If someone tries to create a person with EmployeeID, you should check to see if there is already a record with that reference in the database, with something like

Select count(1) from MYTABLE where COMPANYEMPLOYEEID = ‘1234’

You might have an attribute like ‘Leaving Date’ which you can check…

Select count(1) from MYTABLE where COMPANYEMPLOYEEID = ‘1234’ and LEAVINGDATE is NULL

Never give bad ideas to a customer…

never let a customer dictate the database schema.

My assumption is: the employee number is unique and fixed by the HR.

If a police badge number is used several times, how do you find out the name of the person using it? You have to ask for his first and last name.
In the case of a unique number: no question: we know who we are talking about. The salary increase will go well to the person concerned, no risk of error. ( :wink: )

This may be stipulated in the description of the software.