Cannot add or update a child row

[georgepatotk]

tblSalesItems(SalesItemsPK[PK], SalesItemCode, DiscountPK[FK])

tblDiscount(DiscountPK[PK], DiscountPercent)

 

I am designing my databases in MySQL. In the tblSalesItems, DiscountPK can be NULL due to no discount applied. So, this is mean "zero or one to many relation".

 

But, everytime I perform insertion to tblSalesItems without DiscountPK, an error showing "Cannot add or update a child row: a foreign key contraint fails"

 

Can you please let me know what is the problem?

[Afraits]

Could you explain why you have the tbldiscount in the first place? I'm not sure why you need it.

 

To solve the problem I would avoid the null when no discount is applied and have a 0% discount entry in tbldiscount and populate tblsalesitems with the DiscountPK which matches the 0% entry in tbldiscount.

[georgepatotk]

tblDiscount(DiscountPK[PK], DiscountPercent)

 

the actual design for tblDiscount is

 

tblDiscount(DiscountPK[PK], DiscountPercent, StockCode, DurationFrom, Duration To)

 

Cause from time to time, there will be different discounts for the same items.

Please let me know why the table cannot be zero or one to many relation. I am using InnoDB.

[Afraits]

It will be connected with the foreign key. I would say MySQL is expecting the DiscountPK in tblSalesItems to match a valid entry in tblDiscounts which it can't do because of the null value. I'm not sure about MySQL but for SQL Server, which I normally use, having nulls in foreign key fields is not a good idea. Its depends somewhat on how the constraint has been defined but I expect by having entries in tblDiscounts which correspond to no discount offered will allow you to add the rows into tblSalesItems

[cpopham]

Any table relations, you can set up the foreign key to allow nulls. In this instance, if there is any type of entry in the Foreign Key field, even a 0, it has to match to the Primary key of your parent table, otherwise it will throw an error everytime. I really do not understand this concept because if you set up a child table, you are normalizing the database and you set up the child table to elimnate nulls or N/As in the parent table, In theory, you should not have any entries in the child table that do not have a matching entry in the parent table. Now, if the child table has fields wich will contain nulls, or N/As you should follow through the normalizing procedure and eliminate the null values through this process.

 

If I understand how you have this set up and want it to work, is that you have the primary table which contains your stock codes. When someone purchases or inquires about the stock code, you want to check your child "discount" table to see if an applicable discount rate is there for that particular item. If there is a discount or discounts, you want to check to see when the discount will be discontinued and then apply this discount accordingly. Let me know how far off of the mark I am.

 

If your primary table is based on the stock code, you do not need to duplicate this value in the child table, this can lead to problems. You only need to have the PrimaryKey from your parent table as the foriegn key in your child table. Now with the Parent table in a one to many relationship with the child table, not zero to many, you can add as many discounts for the items as you would like. If you have a discount for an item in the child table, it should have a parent record, if not then you will have orphaned data and this can lead to database integrity issues.

 

I would try to avoid the use of nulls in the child table because it can lead to redunant or orphaned records and jeopardize the integrity of your database.

 

Hope this helps.

 

Chester

[georgepatotk]

cpopham, you did a very good explanation for me. I shouldn't let it NULL since it is in a child table.

 

By the way, I have solve this problem by making DiscountPK in tblSalesItems not a Foreign Key. So, in this way, it won't show the problem anymore.

 

This is kinda not safe because if the users accidently deleted a consumed Discount record, the calculation might be wrong.

 

So, to avoid this, I am doing a verification in the Application Source Code as to make sure a particular Discount record is not being used in tblSalesItems before it can be deleted.