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 --- pj@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=gcdmg-mysql-2@m.gmane.org

Re: cannot alter table - rather urgent

On Thu, May 21, 2009 at 11:06 PM, 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.

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
- mdykman@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=gcdmg-mysql-2@m.gmane.org

Re: cannot alter table - rather urgent

--------------050703090607050301050503
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

>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
>
>

--------------050703090607050301050503--

Re: cannot alter table - solved

Michael Dykman wrote:
> On Thu, May 21, 2009 at 11:06 PM, 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.
>>
>
> 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?
>
>
I was trying to insert some records to fill up empty id numbers and in
the process noticed that there is a primary key in the tables but
unnecessary if I am not mistaken. Also the book_categories.categories_id
should be referencing categories.id -- I think I had somehow wet up the
table erroneously.
The problem was that one of the books was not entered as it should have
and I was assuming it had been entered (2 others were at the same time -
using phpMyAdmin instead of my insert page).
It now works with minimal bugs on the back-end, but the panic is over.
I'll try to fix the primary key issue next.
Thanks for the quick response.

--
Hervé Kempf: "Pour sauver la planète, sortez du capitalisme."
------------------------------------------------------------ -
Phil Jourdan --- pj@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=gcdmg-mysql-2@m.gmane.org

Re: cannot alter table - solved

On Fri, May 22, 2009 at 12:26 AM, PJ wrote:
> Michael Dykman wrote:
>> On Thu, May 21, 2009 at 11:06 PM, 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.
>>>
>>
>> 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?
>>
>>
> I was trying to insert some records to fill up empty id numbers and in
> the process noticed that there is a primary key in the tables but
> unnecessary if I am not mistaken. Also the book_categories.categories_id
> should be referencing categories.id -- I think I had somehow wet up the
> table erroneously.
> The problem was that one of the books was not entered as it should have
> and I was assuming it had been entered (2 others were at the same time -
> using phpMyAdmin instead of my insert page).
> It now works with minimal bugs on the back-end, but the panic is over.
> I'll try to fix the primary key issue next.
> Thanks for the quick response.

I would suggest that the primary key is imoprtant. All relational
tables need a primary key and, in this particular case, the primary
key is what is preventing you from creating duplicate rows.

If anything needs to go:
KEY `fk_book_categories_books` (`bookID`),
bookID, being the first part of your compound primary key, is
effectively indexed already. The key listed above is quite
unnecessary.


--
- michael dykman
- mdykman@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=gcdmg-mysql-2@m.gmane.org

Re: cannot alter table - solved

Michael Dykman wrote:
> On Fri, May 22, 2009 at 12:26 AM, PJ wrote:
>
>> Michael Dykman wrote:
>>
>>> On Thu, May 21, 2009 at 11:06 PM, 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.
>>>>
>>>>
>>> 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?
>>>
>>>
>>>
>> I was trying to insert some records to fill up empty id numbers and in
>> the process noticed that there is a primary key in the tables but
>> unnecessary if I am not mistaken. Also the book_categories.categories_id
>> should be referencing categories.id -- I think I had somehow wet up the
>> table erroneously.
>> The problem was that one of the books was not entered as it should have
>> and I was assuming it had been entered (2 others were at the same time -
>> using phpMyAdmin instead of my insert page).
>> It now works with minimal bugs on the back-end, but the panic is over.
>> I'll try to fix the primary key issue next.
>> Thanks for the quick response.
>>
>
> I would suggest that the primary key is imoprtant. All relational
> tables need a primary key and, in this particular case, the primary
> key is what is preventing you from creating duplicate rows.
>
> If anything needs to go:
> KEY `fk_book_categories_books` (`bookID`),
> bookID, being the first part of your compound primary key, is
> effectively indexed already. The key listed above is quite
> unnecessary.
>
>
Actually, that key (book_categories.bookID) references book.id;
book_categories.categories_id references categories.id. Is'nt it
necessary for both to have foreigh keys? Things so far are working fine...

--
Hervé Kempf: "Pour sauver la planète, sortez du capitalisme."
------------------------------------------------------------ -
Phil Jourdan --- pj@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=gcdmg-mysql-2@m.gmane.org

Re: cannot alter table - solved

You do need the foreign keys for integrity and the columns which make
the foreign reference should be indexed as well. My only point is
that bookID is already indexed as the first element in the primary
key, so the additional index on bookID alone is superfluous.

- michael


On Fri, May 22, 2009 at 3:17 PM, PJ wrote:
> Michael Dykman wrote:
>> On Fri, May 22, 2009 at 12:26 AM, PJ wrote:
>>
>>> Michael Dykman wrote:
>>>
>>>> On Thu, May 21, 2009 at 11:06 PM, 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` (
>>>>> =A0`bookID` smallint(6) unsigned NOT NULL,
>>>>> =A0`categories_id` int(2) unsigned NOT NULL,
>>>>> =A0PRIMARY KEY (`bookID`,`categories_id`),
>>>>> =A0KEY `fk_book_categories_books` (`bookID`),
>>>>> =A0KEY `fk_book_categories_categories` (`categories_id`),
>>>>> =A0CONSTRAINT `book_categories_ibfk_1` FOREIGN KEY (`book_id`) REFERE=
NCES
>>>>> `book` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
>>>>> ) ENGINE=3DInnoDB DEFAULT CHARSET=3Dlatin1
>>>>>
>>>>> Anybody out there still up? I'm rather desperate to fix this this eve=
ning...
>>>>> Thanks in advance.
>>>>>
>>>>>
>>>> We will need a little more information. =A0The table looks sound but i=
s
>>>> clearly designed to link =A02 other tables. =A0If you are failing to
>>>> insert or update, it seems likely that it is because the data is
>>>> absent in the foreign tables. =A0Can you confirm? =A0Because without t=
hat
>>>> forgeign data, these rows are pretty meaningless.
>>>>
>>>> What is it you are trying to do?
>>>>
>>>>
>>>>
>>> I was trying to insert some records to fill up empty id numbers and in
>>> the process noticed that there is a primary key in the tables but
>>> unnecessary if I am not mistaken. Also the book_categories.categories_i=
d
>>> should be referencing categories.id -- I think I had somehow wet up the
>>> table erroneously.
>>> The problem was that one of the books was not entered as it should have
>>> and I was assuming it had been entered (2 others were at the same time =
-
>>> using phpMyAdmin instead of my insert page).
>>> It now works with minimal bugs on the back-end, but the panic is over.
>>> I'll try to fix the primary key issue next.
>>> Thanks for the quick response.
>>>
>>
>> I would suggest that the primary key is imoprtant. =A0All relational
>> tables =A0need a primary key and, in this particular case, the primary
>> key is what is preventing you from creating duplicate rows.
>>
>> If anything needs to go:
>> =A0 =A0 =A0 KEY `fk_book_categories_books` (`bookID`),
>> bookID, being the first part of your compound primary key, is
>> effectively indexed already. =A0The key listed above is quite
>> unnecessary.
>>
>>
> Actually, that key (book_categories.bookID) references book.id;
> book_categories.categories_id references categories.id. Is'nt it
> necessary for both to have foreigh keys? Things so far are working fine..=
..
>
> --
> Herv=E9 Kempf: "Pour sauver la plan=E8te, sortez du capitalisme."
> ------------------------------------------------------------ -
> Phil Jourdan --- pj@ptahhotep.com
> =A0 http://www.ptahhotep.com
> =A0 http://www.chiccantine.com/andypantry.php
>
>



--=20
- michael dykman
- mdykman@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=3Dgcdmg-mysql-2@m.gmane.o rg