Error When Running Failback Script
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings mysql workbench foreign key referenced column and policies of this site About Us Learn more about Stack Overflow
Mysql Workbench Cannot Add Foreign Key Constraint
the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation mysql add foreign key Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up can't add foreign key in mysql? up vote 12 down vote favorite I used MySQL workbench to add a foreign key in a table, but some strange error happened, this is the SQL statement: ALTER TABLE `tansung`.`Declaration` ADD COLUMN `goodsId` INT(11) NOT NULL AFTER `declarationId` , ADD CONSTRAINT `goodsId` FOREIGN KEY (`goodsId` ) REFERENCES `tansung`.`Goods` (`goodsId` ) ON DELETE NO ACTION ON UPDATE NO ACTION , ADD INDEX `goodsId` (`goodsId` ASC) ; When i click apply, the surprise comes out! ERROR 1005: Can't create table 'tansung.#sql-1b10_1' (errno: 150) SQL Statement: ALTER TABLE `tansung`.`Declaration` ADD COLUMN `goodsId` INT(11) NOT NULL AFTER `declarationId` , ADD CONSTRAINT `goodsId` FOREIGN KEY (`goodsId` ) REFERENCES `tansung`.`Goods` (`goodsId` ) ON DELETE NO ACTION ON UPDATE NO ACTION , ADD INDEX `goodsId` (`goodsId` ASC) ERROR: Error when running failback script. Details follow. ERROR 1050: Table 'Declaration' already exists SQL Statement: CREATE TABLE `Declaration` ( `declarationId` int(11) NOT NULL, PRIMARY KEY (`declarationId`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 I can't find out any mistake in logic, even can't understand the error, please give me a help. mysql foreign-keys mysql-error-1005 share|improve this question edited Apr 30 '11 at 17:20 OMG Ponies 199k36356416 asked Apr 22 '11 at 17:22 firefly 88117 1 the error seems pretty self-explanatory; you're trying to create a table called Declaration that already exists... –Brian Driscoll Apr 22 '11 at 17:27 Make sure you are not
log in tour help Tour Start here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Database Administrators Questions Tags Users Badges Unanswered Ask Question _ Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn http://stackoverflow.com/questions/5758106/cant-add-foreign-key-in-mysql from others in the community. Join them; it only takes a minute: Sign up Here's how it works: Anybody can ask a question Anybody can answer The best answers are voted up and rise to the top MySQL - Alter Table Fails As Workbench Tries To Create Existing Table up vote 0 down vote favorite I have been trying http://dba.stackexchange.com/questions/91483/mysql-alter-table-fails-as-workbench-tries-to-create-existing-table to add another column to a table in my database using MySQL Workbench. This generates an 'ALTER TABLE' command which then fails as it fails attempting a 'CREATE TABLE, which it should not be doing as the table already exists! The output is below:- ERROR 1114: The table 'Analysis' is full SQL Statement: ALTER TABLE `Patient`.`Analysis` ADD COLUMN `TestDateTime` DATETIME NULL AFTER `idResults` ERROR: Error when running failback script. Details follow. ERROR 1050: Table 'Patient' already exists SQL Statement: CREATE TABLE `Patient` ( `idAnalysis` int(11) NOT NULL, `idResults` int(11) DEFAULT NULL, `ResultPDF` mediumblob, `ResultCSV` mediumblob ) ENGINE=InnoDB DEFAULT CHARSET=latin1 How can I add this extra column as this database will evolve over time as user requirements expand? Thank you. mysql mysql-workbench alter-table share|improve this question asked Feb 9 '15 at 11:09 MichaelJohn 82111 add a comment| 3 Answers 3 active oldest votes up vote 0 down vote While the CREATE TABLE is a strange error, and should be investigated further and/or reported to see if the failback operation should be done for Workbench, your real problem
Updates: Status: Duplicate Impact on me: None Category:MySQL Workbench: SQL Editor Severity:S2 (Serious) Version:5.2.29 https://bugs.mysql.com/bug.php?id=57951 OS:Any Assigned to: Tags: Alter table; add primary key Triage: Needs Triage: D2 (Serious) View Add Comment Files Developer Edit Submission View Progress Log Contributions [3 Nov 2010 13:37] Marian Zsemlye Description: CanĀ“t add primary key on existing table. Right click on table -> alter table. How to repeat: ERROR 1067: foreign key Invalid default value for 'WHG' SQL Statement: ALTER TABLE `SBS`.`Bskupiny` CHANGE COLUMN `WHG` `WHG` INT(3) NOT NULL DEFAULT NULL , ADD PRIMARY KEY (`WHG`) ERROR: Error when running failback script. Details follow. ERROR 1050: Table 'Bskupiny' already exists SQL Statement: CREATE TABLE `Bskupiny` ( `WHG` int(3) DEFAULT NULL, `Popis` varchar(255) CHARACTER SET add foreign key utf8 DEFAULT NULL, `MB` int(3) DEFAULT NULL, `MBtext` varchar(70) COLLATE utf8_slovak_ci DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_slovak_ci Suggested fix: Normally add primary key to existing table. If I do it with SQL-script, then is everything OK. [3 Nov 2010 13:49] Peter Laursen Obviously this will fail "NOT NULL DEFAULT NULL" Peter (not a MySQL person) [3 Nov 2010 17:02] Valerii Kravchuk Verified on Mac OS X. Obviously setting column as PK (and, thus, NOT NULL) should remove DEFAULT NULL setting, if any is present. [18 Nov 2010 20:30] Alfredo Kojima duplicate of bug #55456 Legal Policies Your Privacy Rights Terms of Use Contact Us Portions of this website are copyright © 2001, 2002 The PHP Group Page generated in 0.021 sec. using MySQL 5.7.12-enterprise-commercial-advanced-log 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