cannot alter table - rather urgent
I have a seemingly impossible situation. I cannot insert values into the tables and I cannot alter or delete the primary key (which should not exist) or delete the foreign keys nor remove the constraint. G search doesn't help. CREATE TABLE `book_categories` ( `bookID` smallint(6) unsigned NOT NULL, `categories_id` int(2) unsigned NOT NULL, PRIMARY KEY (`bookID`,`categories_id`), KEY `fk_book_categories_books` (`bookID`), KEY `fk_book_categories_categories` (`categories_id`), CONSTRAINT `book_categories_ibfk_1` FOREIGN KEY (`book_id`) REFERENCES `book` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 Anybody out there still up? I'm rather desperate to fix this this evening... Thanks in advance. -- Hervé Kempf: Pour sauver la planète, sortez du capitalisme. - Phil Jourdan --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com/andypantry.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: cannot alter table - rather urgent
On Thu, May 21, 2009 at 11:06 PM, PJ af.gour...@videotron.ca wrote: I have a seemingly impossible situation. I cannot insert values into the tables and I cannot alter or delete the primary key (which should not exist) or delete the foreign keys nor remove the constraint. G search doesn't help. CREATE TABLE `book_categories` ( `bookID` smallint(6) unsigned NOT NULL, `categories_id` int(2) unsigned NOT NULL, PRIMARY KEY (`bookID`,`categories_id`), KEY `fk_book_categories_books` (`bookID`), KEY `fk_book_categories_categories` (`categories_id`), CONSTRAINT `book_categories_ibfk_1` FOREIGN KEY (`book_id`) REFERENCES `book` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 Anybody out there still up? I'm rather desperate to fix this this evening... Thanks in advance. We will need a little more information. The table looks sound but is clearly designed to link 2 other tables. If you are failing to insert or update, it seems likely that it is because the data is absent in the foreign tables. Can you confirm? Because without that forgeign data, these rows are pretty meaningless. What is it you are trying to do? -- - michael dykman - mdyk...@gmail.com - All models are wrong. Some models are useful. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: cannot alter table - rather urgent
I cannot insert values into the tables What is the error message? Has the smallint key run out of values? and I cannot alter or delete the primary key (which should not exist) Eh? Without a PK, it ain't a table. or delete the foreign keys nor remove the constraint. G search doesn't help. If the pk referenced by the fk is full, I think you need to drop the fk, then drop the pk in the table referenced by the fk, then recreate that pk as an int, then recreate the fk. PB - PJ wrote: I have a seemingly impossible situation. I cannot insert values into the tables and I cannot alter or delete the primary key (which should not exist) or delete the foreign keys nor remove the constraint. G search doesn't help. CREATE TABLE `book_categories` ( `bookID` smallint(6) unsigned NOT NULL, `categories_id` int(2) unsigned NOT NULL, PRIMARY KEY (`bookID`,`categories_id`), KEY `fk_book_categories_books` (`bookID`), KEY `fk_book_categories_categories` (`categories_id`), CONSTRAINT `book_categories_ibfk_1` FOREIGN KEY (`book_id`) REFERENCES `book` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 Anybody out there still up? I'm rather desperate to fix this this evening... Thanks in advance. No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.5.339 / Virus Database: 270.12.36/2126 - Release Date: 05/21/09 06:22:00
