Apache OpenOffice (AOO) Bugzilla – Issue 107627
Alert when user attempts to create relation between 2 fields of same type but different length.
Last modified: 2013-02-07 22:36:30 UTC
Generate an alert when a user attempts to create a relationship between two fields that are of the same type, but have different lengths. The example I recently encountered was in the creation of a small project management database. I created a Status table, with a single field Status (Text [VARCHAR]). The length was set to 100, and I populated the table with various statuses that can be assigned to a particular task. In the Tasks table, I added a field for Status (Text [VARCHAR]), and accidentally set the length value to 10. In the relationships editor, I was able to assign the relationship between Status.Status and Tasks.Status, but didn't run into the problem until I setup a combobox on my Tasks form for selecting a status to assign to a particular task. When attempting to select a status that was longer than 10 characters, the form would generate an error. My thought was that there should have been an error earlier, when I attempted to create the relationship, informing me that the two fields were of different length values.
Here are a few steps to reproduce the situation I described originally to hopefully clarify things. Create table called Status: Status, Text, length 50, PK Create a table called Task: TaskID, INT, Auto, PK Status, Text, length 10, FK Populate the Status table with the following: Scheduled Cancelled Completed In Progress Create relationship between Status.Status and Tasks.Status (This is where an alert should pop up warning the user that they are creating a bad relationship. Base currently allows the user to create the relationship.) Create a Form for the Task table and add a single Combo Box element to pull data from Status.Status and put it in Task.Status. Leave design mode in the Form and you are now able to add records into the database with Cancelled, Completed, or Schedule statuses, but once you try to create a record with In Progress, the following error is generated: SQL Status: 22001 Error code: -124 Value too long in statement [INSERT INTO "Task" ( "Status") VALUES ( ?)]