Greetings.
I am attempting to create a database to keep track of my customers and my sales to each customer. I currently have organized the database as shown in the image below, but I cannot create a relationship between the fields titled Ordernum in the Sales and Orders tables. I get an error that reads: Integrity constraint violation - no parent 1137905, table: Sales in statement[ALTER TABLE "Sales" ADD FOREIGN LEY ("Ordernum") REFERENCES "Orders" (Ordernum")]
Customer numbers are unique, as are order numbers. However, the relationship between customers and order numbers is a one to many relationship.
I'm not sure where my problem lies - I'm wondering if I should simplify the database, removing the Sales table and simply putting the customer number in with each order. (It's been a LONG time since Database 101... like 27+ years - so I'm trying to recall what to do about normalizing data, etc).
I'll be trying to run reports based on a weekly timeframe, and also running cumulative total reports by customer. Any suggestions would be helpful.
Thanks,
Ross
Integrity Constraint Violation: no parent 1137905
Integrity Constraint Violation: no parent 1137905
OpenOffice 3.1 Windows XP
- Hagar Delest
- Moderator
- Posts: 32655
- Joined: Sun Oct 07, 2007 9:07 pm
- Location: France
Re: Integrity Constraint Violation: no parent 1137905
Hi and welcome to the forum!
I've moved your post from the Beginner forum to the Base section, you will certainly get more help here.
I've moved your post from the Beginner forum to the Base section, you will certainly get more help here.
LibreOffice 7.6.2.1 on Xubuntu 23.10 and 7.6.4.1 portable on Windows 10
-
- Volunteer
- Posts: 828
- Joined: Fri Nov 30, 2007 10:58 am
- Location: Scotland
Re: Integrity Constraint Violation: no parent 1137905
Unless you have a specific need for your Sales table - there does not appear to be one - I would suggest you drop that table and create a direct relationship between customers and orders by adding 'CustomerNum' as a Foreign Key in the Orders table.Customer numbers are unique, as are order numbers. However, the relationship between customers and order numbers is a one to many relationship.
I'm not sure where my problem lies - I'm wondering if I should simplify the database, removing the Sales table and simply putting the customer number in with each order.
When this issue has been resolved, it would help other users of the forum if you add the word - [Solved] - to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
Re: Integrity Constraint Violation: no parent 1137905
The advice of Arineckaig is right. You normally implement a table like sales when you have a many to many relation. E.g. multiple customers can place one order (That might be good for your business when you can sent them all the invoice ).
But I like to focus on the error you are getting. You create a relation beween Sales and Orders tables as a 1:N. When you define that relation the database checks if that relation is valid. The error you are getting is that this relation can not be maintained because you have at least one order that is not mentioned in the sales table. With a simple query you can find out all orders that do not have a match in the sales table:
But I like to focus on the error you are getting. You create a relation beween Sales and Orders tables as a 1:N. When you define that relation the database checks if that relation is valid. The error you are getting is that this relation can not be maintained because you have at least one order that is not mentioned in the sales table. With a simple query you can find out all orders that do not have a match in the sales table:
Code: Select all
SELECT O."Ordernum"
FROM "Orders" O LEFT JOIN "Sales" S ON O."Ordernum" = S."Ordernum"
WHERE S."Ordernum" IS NULL
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
W11 22H2 (build 22621), LO 7.4.2.3(x64)