h3rm1t9536
asked on
foreign key constraint failed in normalised database
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_detai
The distribution_channel table has a 1:m mandatory relationship with orders table. The distribution_channel_detai
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_detai
I have attached the tables to this post showing all the possible values. However distribution_channel_detai
I realise I could easily add both DistributionChannelDetailI
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_detai
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
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_detai
The distribution_channel table has a 1:m mandatory relationship with orders table. The distribution_channel_detai
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_detai
I have attached the tables to this post showing all the possible values. However distribution_channel_detai
I realise I could easily add both DistributionChannelDetailI
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_detai
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
ASKER
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!
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!
ASKER
sorry here is the correct table screen shot that includes IndustrySectorID as a foreign key... 
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?
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?
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.
Ensure that your order table have a correctly defined foreignKey to Industry_sectors table.
ASKER
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?
ASKER
sorry keep uploading the wrong image like a fool! haha! sorry. 
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.
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.
ASKER
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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_detai
orders and distribution_channel have a 1:m mandatory relationship
distribution_channel and distribution_channel_detai
orders has a foreign key DistributionChannelID. However I have still not made a relationship with distribution_channel_detai
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_detai
3. relationship with distribution_channel and distribution_channel_detai
?
I am trying to keep the DB in the most normal form posible.
About the second question (very similar to the first) - involving distribution_channel and distribution_channel_detai
orders and distribution_channel have a 1:m mandatory relationship
distribution_channel and distribution_channel_detai
orders has a foreign key DistributionChannelID. However I have still not made a relationship with distribution_channel_detai
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_detai
3. relationship with distribution_channel and distribution_channel_detai
?
I am trying to keep the DB in the most normal form posible.
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_detai
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.
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_detai
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.
ASKER
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
Is there no other way?
Is there no other way?
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).
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).
ASKER
sorry for the confusion -
distribution_channel has a 1:m relationship with distribution_channel_detai
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?
distribution_channel has a 1:m relationship with distribution_channel_detai
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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.