Integrity Constraint Violation: no parent 1137905

[Ross]

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

[Hagar Delest]

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.

[Arineckaig]

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.

[eremmel]

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

SELECT O."Ordernum"
FROM "Orders" O LEFT JOIN "Sales" S ON O."Ordernum" = S."Ordernum"
WHERE S."Ordernum" IS NULL