Relationships

This topic is empty.

Viewing 7 reply threads

Author

Posts
- June 16, 2003 at 8:55 pm #8037
  
  spc_123
  Member
  
  I have three tables, all Innodb (for example): cs_component:
  
  Field Type Null Key Default Extra
  
  id smallint(8) PRI (NULL) auto_increment
  
  component_type_id smallint(8) YES (NULL)
  
  segment_id smallint(8) YES (NULL)
  
  comp_conf_id smallint(8) YES (NULL)
  
  and cs_component_type:
  
  Field Type Null Key Default Extra
  
  id smallint(8) PRI (NULL) auto_increment
  
  name varchar(20) YES (NULL)
  
  tier_name smallint(2) YES (NULL)
  
  and cs_segment:
  
  Field Type Null Key Default Extra
  
  id smallint(8) PRI (NULL) auto_increment
  
  name varchar(50) YES (NULL)
  
  installation_id smallint(8) YES 0
  
  I'm trying to establish a relationship between cs_component and cs_component_type. I create an index on cs_component with component_type_id, then from cs_component_type, I create a relationship to cs_component. Seems to work fine until I want to add data to cs_component_type, which then results in errors.
  
  If I import via csv file, I get: Error No. 2013 Lost connection to MYsql during query.
  
  If I try to enter data manually into the table, I get: Error No. 1216 Cannot add or update a child row, a foreign key constraint fails.
  
  If I add data to cs_component_type before creating the relationship, I cannot establish a relationship. What am I doing wrong?
- June 17, 2003 at 2:33 am #14504
  
  CalEvans
  Member
  
  WHen you established your relationship did you build any constraints? Sounds like you did and the new rows are violating the constraints.
  
  =C=
- June 17, 2003 at 3:59 am #14505
  
  spc_123
  Member
  
  No, I did not knowingly build any constraints. In fact, if I use DBManager instead of SQLyog, I don't have any problem creating the relationships. In fact, that was the only way I managed to create the foreign key section displayed in the following table. I'd prefer to use SQLyog if possible. Here is the first table info:
  
  Column Information For – cyberdb.cs_component
  
  Field Type Null Key Default Extra
  
  id smallint(8) PRI (NULL) auto_increment
  
  component_type_id smallint(8) YES MUL (NULL)
  
  segment_id smallint(8) YES (NULL)
  
  comp_conf_id smallint(8) YES (NULL)
  
  Index Information For – cyberdb.cs_component
  
  Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
  
  cs_component 0 PRIMARY 1 id A 0 (NULL) (NULL) BTREE
  
  cs_component 1 component_type_id 1 component_type_id A 0 (NULL) (NULL) YES BTREE
  
  DDL Information For – cyberdb.cs_component
  
  Table Create Table
  
  cs_component CREATE TABLE `cs_component` (
  
  `id` smallint(8) NOT NULL auto_increment,
  
  `component_type_id` smallint(8) default NULL,
  
  `segment_id` smallint(8) default NULL,
  
  `comp_conf_id` smallint(8) default NULL,
  
  PRIMARY KEY (`id`),
  
  KEY `component_type_id` (`component_type_id`),
  
  FOREIGN KEY (`component_type_id`) REFERENCES `cs_component_type` (`id`)
  
  TYPE=InnoDB
  
  *************************************************
  
  here is the second:
  
  Column Information For – cyberdb.cs_component_type
  
  Field Type Null Key Default Extra
  
  id smallint(8) PRI (NULL) auto_increment
  
  name varchar(20) YES (NULL)
  
  tier_name smallint(2) YES (NULL)
  
  Index Information For – cyberdb.cs_component_type
  
  Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
  
  cs_component_type 0 PRIMARY 1 id A 13 (NULL) (NULL) BTREE
  
  DDL Information For – cyberdb.cs_component_type
  
  Table Create Table
  
  cs_component_type CREATE TABLE `cs_component_type` (
  
  `id` smallint(8) NOT NULL auto_increment,
  
  `name` varchar(20) default NULL,
  
  `tier_name` smallint(2) default NULL,
  
  PRIMARY KEY (`id`)
  
  TYPE=InnoDB
  
  *************************************************************
  
  Any ideas?
- June 17, 2003 at 6:46 am #14506
  
  Shadow
  Member
  
  Interesting. I created the two tables you specified (without the relationship), then added the given foreign key with SQLyog and entered data to cs_component_type. And it worked. Are you sure that you don't have any other constraints?
- June 17, 2003 at 6:54 am #14507
  
  Shadow
  Member
  
  Pressed 'Add reply' too soon…
  
  So, according to the DDL data given by you, cs_component_type table doesn't have any constraints, only cs_component does. Inserting invalid values into cs_component. component_type_id, that is values not existing in cs_component_type.id, will result in “Error No. 1216 Cannot add or update a child row, a foreign key constraint fails.”
- March 11, 2004 at 4:48 pm #14508
  
  dahuan60
  Member
  
  I have one to one relationship in DB. Why I can' get updated data in child table when I update date in main table? Also, when I add a new data in the main table. my child table doesn't add a new data.
  
  I got Error message : ErrorNo 1216 Cannot add or update row: a foreign key constraint fail.
  
  Please help!
- March 13, 2004 at 10:48 am #14509
  
  Shadow
  Member
  
  We'll need the tables' structure to answer your question!
- March 17, 2004 at 7:57 pm #14510
  
  dahuan60
  Member
  
  CREATE TABLE `svcontract`
  
  `TagID` varchar(6) NOT NULL default '',
  
  `SVCVender` varchar(35) default NULL,
  
  `ContNum` varchar(8) default NULL,
  
  `BegDate` date default NULL,
  
  `EndDate` date default NULL,
  
  `ContName` varchar(40) default NULL,
  
  `PhoneNum` varchar(19) default NULL,
  
  `EMail` varchar(50) default NULL,
  
  PRIMARY KEY (`TagID`)
  
  ) TYPE=InnoDB
  
  and
  
  CREATE TABLE `core` (
  
  `TagID` varchar(6) NOT NULL default '',
  
  `AssetType` char(1) default NULL,
  
  `Categ` varchar(10) NOT NULL default '',
  
  `Description` varchar(20) default NULL,
  
  `Cost` decimal(6,0) default NULL,
  
  `AcqDate` time default NULL,
  
  `Manufac` varchar(35) default NULL,
  
  `ModelNo` varchar(10) default NULL,
  
  `SerNo` varchar(20) default NULL,
  
  `Vender` varchar(35) default NULL,
  
  `WrantyExp` time default NULL,
  
  `SvcCtrt` char(1) default NULL,
  
  `ResponsibleName` varchar(40) default NULL,
  
  `CostrCr` varchar(4) default NULL,
  
  `Bldg` char(2) default NULL,
  
  `Wing` char(3) default NULL,
  
  `Room` varchar(4) default NULL,
  
  PRIMARY KEY (`TagID`) Need create 1-1 relationship.
Author

Posts