MySQL Forums
Forum List  »  Partitioning

Foreign key constraints with partitioning
Posted by: Gharieb Abdel-Mawla
Date: March 25, 2009 06:11AM

When I was creating a table with partitioning and the table has foreign key constraints
the output was "errno:1215 Cannot add foreign key constraint"
CREATE TABLE  `data` (
  `c1` int(10) unsigned NOT NULL,
  `c2` int(10) unsigned NOT NULL,
  `c3` varchar(10) NOT NULL DEFAULT '',
  PRIMARY KEY (`c1`,`c2`) USING BTREE,
  KEY `FK_1` (`c1`),
  KEY `FK_2` (`c2`),
  KEY `FK_3` (`c3`),
  KEY `Index_5` (`c3`) USING BTREE,
  CONSTRAINT `FK_1` FOREIGN KEY (`c1`) REFERENCES `table1` (`c1),
  CONSTRAINT `FK_2` FOREIGN KEY (`c2`) REFERENCES `table2` (`c2`),
  CONSTRAINT `FK_3` FOREIGN KEY (`c3`) REFERENCES `table3` (`c3`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC
PARTITION BY RANGE (c1)
(PARTITION p0 VALUES LESS THAN (19) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (20) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (34) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (35) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (36) ENGINE = InnoDB,
 PARTITION p5 VALUES LESS THAN (37) ENGINE = InnoDB,
 PARTITION p6 VALUES LESS THAN (38) ENGINE = InnoDB,
 PARTITION p7 VALUES LESS THAN (39) ENGINE = InnoDB,
 PARTITION p8 VALUES LESS THAN (63) ENGINE = InnoDB,
 PARTITION p9 VALUES LESS THAN (64) ENGINE = InnoDB,
 PARTITION p10 VALUES LESS THAN MAXVALUE ENGINE = InnoDB)
and when I delete the constraints part
  CONSTRAINT `FK_1` FOREIGN KEY (`c1`) REFERENCES `table1` (`c1),
  CONSTRAINT `FK_2` FOREIGN KEY (`c2`) REFERENCES `table2` (`c2`),
  CONSTRAINT `FK_3` FOREIGN KEY (`c3`) REFERENCES `table3` (`c3`)
the table was created successfully
how can I add a foreign key constraint to partitioned table?

Options: ReplyQuote


Subject
Views
Written By
Posted
Foreign key constraints with partitioning
11898
March 25, 2009 06:11AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.