Problems with MySQL bidirectional relationship and mirrored foreign keys

Question

i have to create two tables with a bidirectional relationship, as in the figure given below. enter image description here But it always gives an error. I am using the following query for creating the tables.

CREATE TABLE IF NOT EXISTS `rpt_operation` (
  `op_id` int(45) NOT NULL,
  `component` int(45) NOT NULL,
  `ideal_time` time NOT NULL,
  `handling_time` time NOT NULL,
  PRIMARY KEY (`op_id`),

  INDEX (component),
  FOREIGN KEY (component)
  REFERENCES rpt_component(comp_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;



CREATE TABLE IF NOT EXISTS `rpt_component` (
  `comp_id` int(45) NOT NULL,
  `lot_code` int(60) NOT NULL,
  `lot_color` varchar(60) NOT NULL,
  `drawing_num` int(60) NOT NULL,
  `revision_num` int(60) NOT NULL,
  `operation` int(45) NOT NULL,
  PRIMARY KEY (`comp_id`),

  INDEX (operation),
  FOREIGN KEY (operation)
  REFERENCES rpt_operation(op_id)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

The error appear in the line component int(45) NOT NULL of rpt_operation table. Any help will be appreciated. Thanks in advance

firstly, this is most likely due to the order of commands, you might create both tables first and then set the indexes on them. Anyway, a "bidirectional" relation, is somtimes a hint to better but both tables into one larger table (cant have a row in rpt_operation without one in rpt_component) as I can't see a break in normalization by doing so. — Najzero, Sep 12 '12 at 11:18

score 4 · Accepted Answer · edited Oct 29 '13 at 22:59

4

Your table structure is impossible. You can't insert any records into rpt_operation because there are no records in rpt_component for the component foreign key, and you can't insert any records into rpt_component because there are no records in rpt_operation for the operation foreign key.

If you make one or both of those fields nullable, then the table structure is still recursive, so you have to add one of the foreign keys manually, for example:

ALTER TABLE rpt_operation
  ADD CONSTRAINT
  FOREIGN KEY (component)
  REFERENCES rpt_component(comp_id);

edited Oct 29 '13 at 22:59

BenMorel

34,448
50
182
322

answered Sep 12 '12 at 11:26

Neil

54,642
8
60
72

1

The cyclic dependency could be solved in other DBMS by using deferred constraints though. – Sep 12 '12 at 11:49

score 0 · Answer 2 · answered Sep 12 '12 at 11:46

0

I would suggest you to use a linking-table to resolve this problem. This is not a viable solution and requires workarounds.

answered Sep 12 '12 at 11:46

RobinUS2

955
6
17

Problems with MySQL bidirectional relationship and mirrored foreign keys

2 Answers2