Solved

foreign key constraint failed in normalised database

Posted on 2011-09-09
16
334 Views
Last Modified: 2012-05-12
Hi there!

I'm working on a web portal that links to a database - users are able to upload and search for data within the database. I am working with XAMPP and the Server version is 5.5.8

I am trying to create a normalised database with foreign keys by using InnoDB. I am currently adding data to the DB and the relations I have created I don't think are correct.

I am not sure if I'm just being silly and the answer is really simple - but I just keep on going in circles. :wtf:

It would be great if some one could help me out!

There are two relationships in question:

QUESTION ONE.

tables involved are orders,  industry_sector, and industry_sub_sector. The industry_sector table has an optional 1:m relationship with the industry_sub_sector. orders table has a mandatory m:1 relationship with the industry_sector table. The industry_sector and the industry_sub_sector tables are complete as those are the only possible values to ever be included. The orders table so far is empty as I don't think I will be able to reference which IndustrySubSectorValue that particular OrderID will have.

Possible Solution 1: I know that I could add another field to the orders table - IndustrySubSectorID - however that would create many NULL values as each order doesnt have to have a IndustrySubSectorValue (the relationship is mandatory).

Possible Solution 2: I could make a bridge entity that would include the following fields: OrdersID, IndustySectorID, IndustrySubSectorID. However that would be creating a relationship with orders. Itsn't that incorrect as industry_sub_sector table should have a relationship industry_sector NOT orders?!

I am not sure if I'm just being silly and the answer is really simple - but I just keep on going in circles. It would be great if you could help me out.

QUESTION 2.

This question is similar to question 1 in that it is about relationship between 3 interlinked tables, namely: orders, distribution_channel, and distribution_channel_details.

The distribution_channel table has a 1:m mandatory relationship with orders table. The distribution_channel_details has an optional 1:m relationship with the distribution_channel table. (basically distribution channel details are the 'notes' on how that particular distribution channel was used for that specific order)

Possible solution would be to do the same as question 1 - but that would create null values as each order doesnt require a DistributionDetailValue.


So basically: II am unable to add rows to the orders table because foreign key constraint failed. The relationships I have in place are wrong. I am looking for help with the relationships between:

1. orders, industry_sector, and industry_sub_sector
2. orders, distribution_channel, and distribution_channel_details


I have attached the tables to this post showing all the possible values. However distribution_channel_details is empty as those value will be different for each order added to the database. orders table is empty as well as I am unable to add data because foreign key constraint failed.

I realise I could easily add both DistributionChannelDetailID and IndustrySubSectorID to the orders table - however that would creat a large amount of NULL values as both DistributionChannelDetailValue and IndustrySubSectorValue are optional for each order added.

So big question : do I create a bridge entity that is directly linked to the orders table for the industry_sub_sector and the distribution_channel_details respectively ??

If you need any more detail about either question please let me know, and sorry if I havent explained it all to clearly ;)

Thanks so much! Chelsea
tables.png
0
Comment
Question by:h3rm1t9536
  • 8
  • 8
16 Comments
 
LVL 19

Expert Comment

by:Bardobrave
ID: 36509329
Well.... at first glance I see something that is strange to me:

You say you have a 1:m relationship between industry_sectors and orders, so many orders can come from any industry sector and any order has to come from a given industry sector.

However, your table orders hasn't a foreign key to industry_sectors, you only have a field named industrySubSector wich I supose is a foreign key to industry_sub_sectors table.

If you are trying to infere the relationship between orders and industry_sectors through the transitional relationship between orders and industry_sub_sectors and the relationship between industry_sectors and industry_sub_sectors you'll have a problem, because this last relationship is not mandatory, and so you'll receive an error each time you try to create an order through a subsector if there isn't an associated sector (wich logically shouldn't happen, but your database allow it).

What I think you should do is the opossite, I mean: your order table should have a foreign key to industry_sectors, and then, infere the relationship with subsectors transitionally through the relationship between sectors and subsectors.
0
 

Author Comment

by:h3rm1t9536
ID: 36509483
Thanks so much for the quick reply! :)

the orders table does have a foreign key IndustrySectorID that links orders table to the industry_sector table.

There relationship is:

1. An IndustrySectorValue can appear in many orders, but each order can only refer to one IndustrySectorValue. (Mandatory)

2. Each IndustrySubSectorValue belongs to one IndustrySectorValue, and each IndustrySectorValue can have many IndustrySubSectorValues. (Optional)

Sorry I'm still new at SQL - what do you mean exactly by 'infere the relationship with subsectors transitionally through the relationship between sectors and subsectors'?

Do I create another entity( a bridge entity) where do I put it? between industry_sector and industry_sub_sector? What are the fields that I should include in the new entity? And I must remove the foreign key IndustrySubSectorID from orders table to ensure the DB is normalised?

Thanks so much again for your help!
0
 

Author Comment

by:h3rm1t9536
ID: 36509522
sorry here is the correct table screen shot that includes IndustrySectorID as a foreign key... The correct orders table The correct orders table
0
 
LVL 19

Expert Comment

by:Bardobrave
ID: 36509529
What I mean with the inference is that if you know the subsector of an order and you know the sector of the subsector, then you know the sector of the order without needing the direct relationship, but this will be only true if your relationship between sectors and subsectors would be mandatory.

I don't think you need another entity (bridge entities usually are used to create many to many relationships), your problem here should come from a bad definition of the relationships or from letting a foreignkey empty when creating the record.

When I watch at the capture you provided I don't saw IndustrySectorID on order table, so I thought your problem must be there.

Check your relationships definitions and ensure you are not letting any foreign key empty when trying to add a record to orders. Also I think the error should point to which relationship has the problem, could you put here the complete error you receive?

0
 
LVL 19

Expert Comment

by:Bardobrave
ID: 36509541
None of the two order tables you've just uploaded has an IndustrySectorId field, you only have one IndustrySubSectorId.

Ensure that your order table have a correctly defined foreignKey to Industry_sectors table.
0
 

Author Comment

by:h3rm1t9536
ID: 36509556
so must I include the field IndustrySubSectorID in the orders table? If not how will one be able to know which IndustrySubSector (if there is one) that order belongs to? If I keep IndustrySubSectorID in the orders table wont that create NULL values as it is an optional relationship - wont that inturn make the DB unnormalised?
0
 

Author Comment

by:h3rm1t9536
ID: 36509566
sorry keep uploading the wrong image like a fool! haha! sorry. the correct correct table
0
 
LVL 19

Expert Comment

by:Bardobrave
ID: 36509700
You need to have the field on orders to be able to know wich subsector is related to an order, as you cannot infere it through sectors because every sector has 0 to many subsectors.

Of course, when an order cames from a sector without subsectors, or when an order hasn't a subsector associated, your table will record a NULL value in that foreignKey. What I'm seeing is that you don't have a relationship between orders and subsectors (you don't state it on your question at least), and it should be one.

If your orders HAVE TO come from a subsector, then this should be a mandatory relationship and you can avoid your relationship between orders and sectors because you will be able to infere the sector through the subsector.

However, as you say order-sectors is mandatory and sector-subsector is optional, orders-subsectors cannot be mandatory and you'll need to maintain both relationships in orders table (orders-sector and orders-subsector).

Sorry if I'm not being too clear, I'm thinking with the keyboard and probably my line of think could result a bit odd when readed.
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:h3rm1t9536
ID: 36509755
no worries - so happy your helping me out!

So just to be clear I must include both IndustrySectorID and IndustrySubSectorID in the orders table.
Then I would have two relationships:
1. orders and industry_sector
2. orders and industry_sub_sector

So there would be no relationship between industry_sector and industry_sector.

Thats correct?
0
 
LVL 19

Accepted Solution

by:
Bardobrave earned 500 total points
ID: 36509777
I think in this environment you need three relationships:

1. orders and industry_sector --> mandatory as an order HAS TO come from an industry sector.
2. orders and industry_subsectors --> optional as an order CAN come from an industry subsector.
3. industry_sector and industry_subsectors --> optional as a sector CAN have subsectors.

If the relationship between sectos and subsectors were mandatory (thus a sector had at least one subsector always) you could get rid of orders-subsectors relationship, as you could transitionally infere the values.

In this situation, and if I've understanded well your needings, you need the three relationships, although many times it will be somewhat redundant.
0
 

Author Comment

by:h3rm1t9536
ID: 36509822
Thanks so much for your help! You explained it perfectly!

About the second question (very similar to the first) - involving distribution_channel and distribution_channel_details and orders

orders and distribution_channel have a 1:m mandatory relationship

distribution_channel and distribution_channel_details have a 1:1 optional relationship

orders has a foreign key DistributionChannelID. However I have still not made a relationship with distribution_channel_details and any table. Logically it would be connected to distribution_channel as it stores values (details) about how that specific distribution_channel was used. Each value will be free text and be unique for each order. However it is an optional field for each order.

Do I create a similar relationship to that of question 1? And have 3 relationships:
1. relationship with orders and distribution_channel
2. relationship with orders and distribution_channel_details
3. relationship with distribution_channel and distribution_channel_details

?

I am trying to keep the DB in the most normal form posible.
0
 
LVL 19

Expert Comment

by:Bardobrave
ID: 36509975
In this case the direction of the relationships is different than the fist case.

Here orders is related 1:m to channels, so an order can have many possible channels, and every channel can have or not details.

Actually, with your previous description I think that your distribution_channel_details should be only a nullable text field on your distribution_channel table, as it only contains one text field and is 1:1

Of course this erases completelly the need of a relation between orders and details, because it's contained in the relation between orders and channels.
0
 

Author Comment

by:h3rm1t9536
ID: 36510367
The thing is the distribution_channel has only 11 possible values - users can not add more value to this table. Having the distribution_channel table contain DistributionChannelID DistributionChannelValue and DistributionChannelDetails will thus create redundencies as all 11 possible DistributionChannelValue's will be repeated with each unique DistributionChannelDetail that belongs to each order. The database needs to be normalised...

Is there no other way?
0
 
LVL 19

Expert Comment

by:Bardobrave
ID: 36510994
I must be losing something here...

If you say you have a 1:1 optional relationship between channels and details, and you say you have only 11 possible values at channels, you must have no more than 11 different values at details.

What you define in your last post sounds more like if details is a field of order table and has no direct relation to channels (no more than that infered from the relationship between orders and channels).
0
 

Author Comment

by:h3rm1t9536
ID: 36511428
sorry for the confusion -
distribution_channel has a 1:m relationship with distribution_channel_details:  each distribution_channel can appear in many distribution_channel_details, but each distribution_channel_detail can relate to only one distribution_channel (optional)

There are only 11 possible values in the distribution_channel  - users can not add more value to this table.

Sorry for the confusion... what relationships do you think is best?
0
 
LVL 19

Assisted Solution

by:Bardobrave
Bardobrave earned 500 total points
ID: 36516002
Then you probably need the three relationships.

You need the relationship between orders and channels because you cannot infere it through details as channels - details is optional.

You need the relationship between orders and details for the same reason.
And of course you need a relationship between channels and details.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The viewer will learn how to dynamically set the form action using jQuery.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now