Most common error message when importing data and how to resolve them.
One of the hardest challenges when working with a database management system is mastering the art of importing data.
Databases have built in rules that prevent erroneous data form entering the system and a set of common error messages that are meant to guide the users when “dirty” data cannot be inserted into the system.
These error messages can be difficult to understand for a person that does not have a background in databases, and these are the most common error messages, what they mean and how to get around them.
-
PK violation (Primary Key Violation Error)
- As we can see from the error message above, databases unfortunately send very long and wordy error messages.
- But if we know what we are looking for, it can be very easy to know what is going wrong.
- The only part we need from the entire block of text is the following: "Violation of the PRIMARY KEY constraint, cannot insert duplicate key"
- This error message simply states that we cannot insert the current record, because it is already in the database and the database cannot creates duplicates.
-
DateTime format is incorrect
- Unfortunately, databases require date and time data in a specific format.
- As we can see from the error message, they database was unable to use the date in the format that we supplied it, in the file.
- To check what format is required, you can click on the Config button in Geobank and in the configuration manager look for the DataSource's item.
- Right click on the data source that is in use and select properties from the menu
- You can navigate on the left to the Environment Settings and see the date format at the top of the screen.
- Update the data in the sheet to match the format and this will resolve the error.
- Unfortunately, databases require date and time data in a specific format.
-
String or Binary data will have to be truncated.
- The columns in a database table have a set number of characters that they can store.
- If we try to push data into a column that goes over the maximum number of characters that can be stored in that column, the database will produce this error.
- In the event that we receive this error, we will have to go back into our file and find the column where the characters are too much.
- The visual query builder in Geobank can be used to assist with this, as it does tell us the number characters allowed in every column of a table as can be seen below.
Want to learn more?
Online Help Manuals - Click here for the latest version
Learning Management System - Click here to login or here to request access
Comments
0 comments
Please sign in to leave a comment.