Referential Integrity: Cannot add new record

[smti]

Hello - 

 

I am having a problem adding a new record to a table named: WizardChoices. When I execute the query I receive the following error:

Cannot add or update a child row: a foreign key constraint fails (`resticket`.`WizardChoices`, CONSTRAINT `WizardChoices_ibfk_3` FOREIGN KEY (`sub_problem_type`) REFERENCES `SubProblemTypes` (`type_name`) ON DELETE SET NULL ON UPDATE NO ACTION)

How can I correct this? Here is the structure of both tables; the relationship view is also attached.

describe SubProblemTypes;
+-----------------------+-------------+------+-----+---------+----------------+
| Field                 | Type        | Null | Key | Default | Extra          |
+-----------------------+-------------+------+-----+---------+----------------+
| type_id               | int(3)      | NO   | PRI | NULL    | auto_increment |
| type_name             | varchar(25) | NO   | MUL | NULL    |                |
| assoc_primary_problem | int(3)      | NO   | MUL | NULL    |                |
| type_description      | varchar(25) | NO   |     | NULL    |                |
+-----------------------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> describe WizardChoices;
+-----------------------+-------------+------+-----+---------+----------------+
| Field                 | Type        | Null | Key | Default | Extra          |
+-----------------------+-------------+------+-----+---------+----------------+
| choice_id             | int(3)      | NO   | PRI | NULL    | auto_increment |
| assoc_question_number | varchar(3)  | NO   | MUL | NULL    |                |
| choice_text           | text        | NO   |     | NULL    |                |
| primary_problem_type  | varchar(50) | NO   | MUL | NULL    |                |
| sub_problem_type      | varchar(50) | YES  | MUL | NULL    |                |
+-----------------------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

 Any assistance would be greatly appreciated!

 

Thanks!

[mikosiko]

the error means that you are trying to insert a record in the table Wizarchoices and the field "type_name" doesn't exists in the table SubProblemTypes... but looking your tables descriptions clearly you are using the FK's incorrectly... in reality in your table WizardChoices the FK "sub_problem_type" should point to the field "type_id" of your table SubProblemTypes and match the size of the field too ... same mistake for the table PrimaryProblemTypes.. referencing the wrong field...

 

mysql> describe WizardChoices;
+-----------------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+-------------+------+-----+---------+----------------+
| choice_id                         | int(3)              | NO | PRI | NULL | auto_increment |
| assoc_question_number | varchar(3)      | NO | MUL | NULL | |
| choice_text                      | text                 | NO | | NULL | |
| primary_problem_type     | INT(3)            | NO | MUL | NULL | |    ///// FK to PrimaryProblemTypes table field "type_id" (guessing field name) .. ON DELETE constraints should be "NO ACTION"
| sub_problem_type           | INT(3)            | YES | MUL | NULL | |  /////  FK to SubProblemTypes table field "type_id"
+-----------------------+-------------+------+-----+---------+----------------+

Edited May 7, 2013 by mikosiko

[smti]

The field: type_name does in fact exist in the SubProblemTypes table. Do you mean that a value must exist before the record can be created?

 

-smti

[mikosiko]

yes... sorry I wasn't clear.... the value for the field must exist before it could be referenced in other table, but as I said... that is only part of your problem

Edited May 7, 2013 by mikosiko

[jazzman1]

@mikosiko want to say that the type and the value of sub_problem_type must be the same as the column type_name from the first table. 

 

Just change sub_problem_type (50) to sub_problem_type (25)

[mikosiko]

@mikosiko want to say that the type and the value of sub_problem_type must be the same as the column type_name from the first table. 

 

Just change sub_problem_type (50) to sub_problem_type (25)

NO... that is no what I want to say... he is using the wrong field to establish the relationship.... sub_problem_type must point to the field type_id of the table SubProblemTypes and not to the field type_name , otherwise if in the future he decide to change the value of that field (name) all the relationship could be broken unless an UPDATE CASCADE is in place (that could be highly inefficient depending on the record volume in the table WizardChoices) ... using the type_name field is not the logical way to establish the relationship.  Same apply to the field primary_problem_type . 

Edited May 7, 2013 by mikosiko

[jazzman1]

@mikosiko, we are not talking about the database logic model here, but if so.... be free and give him a good example, how to do this.

 

I just want to say, that the table contains the foreign key must contain the same type and value as the reference's table.

[mikosiko]

@mikosiko, we are not talking about the database logic model here, but if so.... be free and give him a good example, how to do this.

 

 

No?.. then you are only giving half or less of the advice to the user... we are supposed to came here to offer professional advice to new users ... telling half of the tale, even realizing that the user is doing things incorrectly or that could cause him trouble in the future is not completely professional IMHO  ... your millage can be different.

 

I just want to say, that the table contains the foreign key must contain the same type and value as the reference's table.

 

which is EXACTLY what I did say in my first post.

 

anyway... I think that the user has enough information already... point closed for me.