Integrity Constraint Violation: no parent 1137905

Discuss the database features

Integrity Constraint Violation: no parent 1137905

Postby Ross » Mon Dec 28, 2009 5:09 am

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")]

CustTrackRelationships.jpg

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
OpenOffice 3.1 Windows XP
Ross
 
Posts: 1
Joined: Mon Dec 28, 2009 4:53 am

Re: Integrity Constraint Violation: no parent 1137905

Postby Hagar Delest » Tue Dec 29, 2009 10:11 pm

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.
AOO 4.0.1 on Windows 7 and Xubuntu 14.04 (Trusty Tahr)
User avatar
Hagar Delest
Moderator
 
Posts: 25106
Joined: Sun Oct 07, 2007 9:07 pm
Location: France

Re: Integrity Constraint Violation: no parent 1137905

Postby Arineckaig » Tue Dec 29, 2009 10:50 pm

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.


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.
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).
OOo 3.4.1 on MS Windows XP MySQL and HSQLDB
Arineckaig
Volunteer
 
Posts: 600
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

Re: Integrity Constraint Violation: no parent 1137905

Postby eremmel » Wed Dec 30, 2009 12:32 pm

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:
Code: Select all   Expand viewCollapse view
SELECT O."Ordernum"
FROM "Orders" O LEFT JOIN "Sales" S ON O."Ordernum" = S."Ordernum"
WHERE S."Ordernum" IS NULL
Please, edit this thread's initial post and add "[Solved]" to the subject line if your problem has been solved.
OOo 3.3.0 on XP SP3 for real life with ORB; AOO 3.4.1, 4.0.1, LO 4.1.2.3 on W7 for testing
User avatar
eremmel
Volunteer
 
Posts: 656
Joined: Tue Dec 30, 2008 1:15 am
Location: Barneveld, Netherlands


Return to Base

Who is online

Users browsing this forum: No registered users and 10 guests