Integrity constraint violation error messages.

[JARogers]

This is my first post to this forum so please bear with me as I am a new Open Office user and not a database expert by any means.

Maybe 15 years ago I managed to learn the DOS version of Paradox well enough to design a relational database to hold my book company customer information and print out invoices. I linked four tables: Addresses, Orders, Books and Mailings. Although it was a DOS program, Paradox migrated and worked well for me through Windows 3.1, 95 and XP. I knew I'd have to give it up some day but the idea of learning a new Windows database program put me off. Then recently I decided it was time to update my 6 year old XP desktop and bought a new PC with Vista which does not support DOS programs. I have several DOS programs which I love that have travelled with me over the years and the fact that Vista is not backwards compatible is very frustrating to me but that's another problem.

In my attempt to bring my Paradox tables into the new Windows Vista environment, I decided to try Open Office Base, but I am not having much success. After exporting the Paradox tables to .dbf files and copying and pasting them into Base Open Document File format, I can then edit each table independently, but when I try to link them with tools/relationships, I begin getting all kinds of error messages. One I see frequently is "Error entering new record. Interity Constraint Violation - no parent SYS_FK_269 table:" followed by a string of code. So I tried starting from scratch by designing each table with the same names and rows and columns as the originals but empty of data. Then I used tools/relationships to link them together as they were in Paradox. Up to this point things seem to work fine until I start entering data in the tables. Then I start to get those error messages again. Unlinking them, I can enter data fine, but as soon as they are linked, no can do.

Is there anywhere I can get details about what these error messages mean and how to correct my relationship linking problems? All the Open Office help files and user manuals I've seen so far seem to be very basic compared to the volumes of detailed information available to non experts like me to figure out Paradox.

[Safway]

Here are my thoughts (though maybe not answers for you):
A decimal in one databases doesn't necessarily have the same characteristic of a decimal in another database. This is well-known in the database industry and conversions need to happen when migrating from one database to another. This process is not for the faint of heart.

Are you using the native HSQL database withing OpenOffice? That information can be found in the information bar (probably at the bottom of your OpenOffice screen) when you have the database open.

Can you attach a screenshot of your relationships to this issue? That information can be found by opening the database and then selecting Tools and then Relationships.

This link might be a life saver to you as well:
http://searchenterpriselinux.techtarget ... 86,00.html

[JARogers]

Thanks for your quick input! I will check out the web link you included in your message and yes the database is a native HSQL database within OpenOffice. I am attaching a screen shot of the tools/relationships I am trying to create. As I said in my original post, these tables were empty when I drew the realtionships, but when there is data in them the relationships can't be drawn, or when I try to enter data in the empty tables after drawing the relationships, I get the Integrity constraint violation error messages.

[Safway]

Sure thing on the quick responce. This is one way I repay for the "free" software.

I don't see anything wrong with your table relationships

I would ensure the data in the tables fits the description of the data type *FOR THE HSQL* database. This is probably your problem--you are trying to get data into a field that has been constrained to be another type of data. This might be helpful:
http://java.sun.com/docs/books/tutorial ... types.html
http://www.hsqldb.org/doc/guide/ch09.ht ... es-section
Also read the "SET REFERENTIAL INTEGRITY" part of this document as well
You can do this via Tools | SQL | Type in "SET REFERENTIAL_INTEGRITY FALSE;" | Execute

A side note to be taken not as criticism but hopefully as a helpful note: In your Orders, Issues, and Mailings tables you might want to have a unique ID of "ID" and set it to be your primary key just like you already have in your Address0 table. This will help in the future if you ever want to do more than very simply stuff. Also, break your Customer (or any other) field out into its parts, for example Customer_FirstName, Customer_LastName, etc. This will also payoff in the long run for the same reason. Then again, I could be reading things wrong and this structure has been working for you for a long time already.