Mysql Error 1217 Partition
Contents |
Community Podcasts MySQL.com Downloads Documentation Section Menu: MySQL Forums :: Partitioning :: alter table partition error New Topic Advanced Search alter table partition error Posted
Mysql Partitioning Foreign Key
by: jason zhang () Date: June 30, 2009 04:13AM I created two mysql drop foreign key tables to reproduce this problem: test: id int(11) primary key region_id int(11) region: id int(11) mul index name char(32) then create a constraint: alter table test add constraint foreign key (region_id) references region (id); Up to now, both two tables are empty, I executed below sql to create partition: alter table test partition by hash(region_id) partitions 2; I got below error info: ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails I can not understand why this operation raise an constraint error. There is no record been deleted or updated at all. The real case is I already has a table include millions record and I want to partion on it now. Navigate:Previous Message•Next Message Options:Reply•Quote Subject Views Written By Posted alter table partition error 8213 jason zhang 06/30/2009 04:13AM Re: alter table partition error 3788 jason zhang 06/30/2009 09:08PM Sorry, you can't reply to this topic. It has been closed. powered by phorum 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. Contact Sales USA: +1-866-221-0634 Canada: +1-866-221-0634 Germany: +49 89 143 01280 France: +33 1 57 60 83 57 Italy: +39 02 249 59 120 UK: +44 207 553 8447 Japan: 0120-065556 China: 10800-811-0823 India: 0008001005870 More Countries» Contact Us Online» Products MySQL Enterprise Edition MySQL Standard Edition MySQL Classic Edition MySQL Cluster CGE MySQL Embedded (OEM/ISV) Services Training Certification Consulting Support Downloads MySQL Community Server MySQL Cluster MySQL Fabric MySQL Utilities MySQL Workbench About MySQL Contact Us How to Buy Partners Job Opportunities
this database you can guess which of the tables within it are going to be huge. For those huge tables you want to plan ahead to make sure that you can scale out your data such that you have less headaches in the future. Here’s the catch: you want to keep it simple until it comes time to scale out. That sounds like an honorable goal, right? Now how exactly do we do that…hmm. This post is how I would approach this problem. I hope you http://forums.mysql.com/read.php?106,269536,269536 can gain something from it. I certainly learned some things in trying to get this stuff to work! To ground this problem scenario into something concrete let’s assume that we’re modeling a simple relationship - players and games in a multiplayer game. Here’s the model that we’re after: Multiplayer game ER diagram Here is the DDL to create this model: CREATE TABLE http://antzucaro.com/posts/2011/planning-ahead-for-mysql-partitioning/ IF NOT EXISTS `parttest`.`player` ( `player_id` INT NOT NULL , `name` VARCHAR(30) NULL , PRIMARY KEY (`player_id`) ) ENGINE = InnoDB; CREATE TABLE IF NOT EXISTS `parttest`.`game` ( `game_id` INT NOT NULL , `duration` TIME NULL , `start_dt` TIMESTAMP NOT NULL , PRIMARY KEY (`game_id`) ) ENGINE = InnoDB; CREATE TABLE IF NOT EXISTS `parttest`.`player_game` ( `player_id` INT NOT NULL , `game_id` INT NOT NULL , `create_dt` TIMESTAMP NOT NULL , PRIMARY KEY (`player_id`, `game_id`) , INDEX `player_game_fk01` (`player_id` ASC) , INDEX `player_game_fk02` (`game_id` ASC) , CONSTRAINT `player_game_fk01` FOREIGN KEY (`player_id` ) REFERENCES `parttest`.`player` (`player_id` ) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `player_game_fk02` FOREIGN KEY (`game_id` ) REFERENCES `parttest`.`game` (`game_id` ) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; In this model we have players and games. Many players can participate in a single game and many games can be played by a single player, thus the two tables “player” and “game” have a many-to-many relationship. To represent that in the database we have an associative table (“player_game”) between the two. Also included in two of the tables a
1217. Cannot delete or update a parent row: a foreign https://www.euperia.com/development/fix-for-mysql-error-code-1217-a-foreign-key-constraint-fails/1303 key constraint fails' you need to toggle the foreign_key_checks variable first. The reason for this error is because your tables have foreign keys which rely on the table you're attempting to change. Here's the fix for MySQL error Code 1217 - A foreign key constraint fails: SET foreign_key_checks = 0; DROP TABLE `stats`; SET foreign_key_checks foreign key = 1; I hope this helps someone else. « Inspecting jQuery event handlersJob Control Commands: fg, bg and ctrl-z » AM Andrew McCombe Magento Certified Website Developer Staffordshire, UK Follow @euperia Twitter Google+ Github Latest Posts Magento Snippet: Use a Group By clause on a collection model Managing Homebrew Services I'm a Magento Certified mysql error 1217 Developer! Fix for PHP Composer "????%" issue Introducing Hit The Ground: Laravel Edition Magento Custom Variables MySQL Foreign Key Tutorial Testing Magento Email with Mailtrap.io How to backup and restore with Magento 2 Product and Breadcrumb Rich Snippets in Magento Magento Snippet: How to set and get cookies in Magento Solved: PHP Imagick ‘unable to open image' PDF Magento Snippet: Admin grid currency type with currency symbol My Current Podcasts TIL: MySQL FLUSH PRIVILEGES isn't needed MySQL fix: Field ‘xxxx' doesn't have a default value 20 years of PHP #20yearsofphp Useful Oh-My-Zsh plugins Install Beanstalkd on Ubuntu 12.04 How to pear install HTTP2 and HTTP_Request2 Categories Development Linux Magento Miscellaneous Music Review Website Performance About Ads: Whilst we all hate adverts, this site needs to be funded somehow. Therefore you will only ever see one advert per page on this site. If you're using an ad-blocker, please consider unblocking this site. Thanks. Twitter Google+ Github Copyright © Andrew McCombe 1972 - 2016