Why Does This Drop Fail?

am 22.07.2010 15:02:45 von Victor Subervi

Re: Why Does This Drop Fail?

am 22.07.2010 15:17:33 von John Daisley

--0016364579b8bdde31048bf9bd39
Content-Type: text/plain; charset=ISO-8859-1

Most likely a foreign key constraint would be violated if the table were
dropped. Check those index definitions on `Store` and `User` columns.

Regards

John Daisley

Microsoft SQL Server 2005/2008 Database Administrator
Certified MySQL 5 Database Administrator
Certified MySQL 5 Developer
Cognos BI Developer

Telephone: +44 (0)7918 621621
Email: john.daisley@butterflysystems.co.uk

On 22 July 2010 14:02, Victor Subervi wrote:

> Hi;
>
> mysql> alter table personalData drop foreign key Store;
> ERROR 1025 (HY000): Error on rename of './test/personalData' to
> './test/#sql2-14ce-a61' (errno: 152)
> mysql> describe personalData;
>
> +-------------------+------------------+------+-----+------- -----+----------------+
> | Field | Type | Null | Key | Default |
> Extra |
>
> +-------------------+------------------+------+-----+------- -----+----------------+
> | ID | int(10) unsigned | NO | PRI | NULL |
> auto_increment |
> | Store | varchar(40) | NO | MUL | NULL
> | |
> | User | varchar(50) | NO | MUL | NULL
> | |
> | FirstName | varchar(100) | NO | | NULL
> | |
> | LastName | varchar(100) | NO | | NULL
> | |
> | Phone | varchar(13) | YES | | NULL
> | |
> | Cell | varchar(13) | YES | | NULL
> | |
> | Fax | varchar(13) | YES | | NULL
> | |
> | AddressID | int(11) | YES | | NULL
> | |
> | ShippingAddressID | int(11) | YES | | NULL
> | |
> | DOB | date | YES | | 2000-01-01
> | |
> | Email | varchar(100) | NO | | NULL
> | |
> | PW | varchar(12) | NO | | NULL
> | |
>
> +-------------------+------------------+------+-----+------- -----+----------------+
> 13 rows in set (0.00 sec)
>
> How drop it?
> TIA,
> Victor
>

--
John Daisley

Certified MySQL 5 Database Administrator
Certified MySQL 5 Developer
Cognos BI Developer

Telephone: +44 (0)7918 621621
Email: john.daisley@butterflysystems.co.uk

--0016364579b8bdde31048bf9bd39--

Re: Why Does This Drop Fail?

am 22.07.2010 15:26:13 von Victor Subervi

--0022158df38fc25534048bf9dc5d
Content-Type: text/plain; charset=ISO-8859-1

On Thu, Jul 22, 2010 at 8:47 AM, John Daisley wrote:

> Most likely a foreign key constraint would be violated if the table were
> dropped. Check those index definitions on `Store` and `User` columns.
>

But I don't want to drop the table, I want to drop the foreign key on Store.
How?
TIA,
V

>
> Regards
>
> John Daisley
>
> Microsoft SQL Server 2005/2008 Database Administrator
> Certified MySQL 5 Database Administrator
> Certified MySQL 5 Developer
> Cognos BI Developer
>
> Telephone: +44 (0)7918 621621
> Email: john.daisley@butterflysystems.co.uk
>
> On 22 July 2010 14:02, Victor Subervi wrote:
>
>> Hi;
>>
>> mysql> alter table personalData drop foreign key Store;
>> ERROR 1025 (HY000): Error on rename of './test/personalData' to
>> './test/#sql2-14ce-a61' (errno: 152)
>> mysql> describe personalData;
>>
>> +-------------------+------------------+------+-----+------- -----+----------------+
>> | Field | Type | Null | Key | Default |
>> Extra |
>>
>> +-------------------+------------------+------+-----+------- -----+----------------+
>> | ID | int(10) unsigned | NO | PRI | NULL |
>> auto_increment |
>> | Store | varchar(40) | NO | MUL | NULL
>> | |
>> | User | varchar(50) | NO | MUL | NULL
>> | |
>> | FirstName | varchar(100) | NO | | NULL
>> | |
>> | LastName | varchar(100) | NO | | NULL
>> | |
>> | Phone | varchar(13) | YES | | NULL
>> | |
>> | Cell | varchar(13) | YES | | NULL
>> | |
>> | Fax | varchar(13) | YES | | NULL
>> | |
>> | AddressID | int(11) | YES | | NULL
>> | |
>> | ShippingAddressID | int(11) | YES | | NULL
>> | |
>> | DOB | date | YES | | 2000-01-01
>> | |
>> | Email | varchar(100) | NO | | NULL
>> | |
>> | PW | varchar(12) | NO | | NULL
>> | |
>>
>> +-------------------+------------------+------+-----+------- -----+----------------+
>> 13 rows in set (0.00 sec)
>>
>> How drop it?
>> TIA,
>> Victor
>>
>
>
>
> --
> John Daisley
>
> Certified MySQL 5 Database Administrator
> Certified MySQL 5 Developer
> Cognos BI Developer
>
> Telephone: +44 (0)7918 621621
> Email: john.daisley@butterflysystems.co.uk
>

--0022158df38fc25534048bf9dc5d--

Re: Why Does This Drop Fail?

am 22.07.2010 15:39:38 von John Daisley

--00221546ed66bd4475048bfa0c3b
Content-Type: text/plain; charset=ISO-8859-1

Sorry, my bad! Must learn to read the whole message!!

This can be caused because when a foreign key is created mysql adds an index
key to the column in addition to the foreign key. Why I'm not sure, but
I'm guessing its for performance. To drop this foreign key

First do this to get the index names

SHOW CREATE TABLE `table_name`

Which will give an output something like this

| table_name | CREATE TABLE `table_name` (
`id` int(10) unsigned NOT NULL auto_increment,
`idx` int(5) unsigned NOT NULL,
KEY `key_column` (`idx`),
CONSTRAINT `table_name_ibfk_1` FOREIGN KEY (`idx`) REFERENCES
`second_table` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

So, first you remove the key:

ALTER TABLE table_name DROP KEY `idx`;

then the foreign key:

ALTER TABLE table_name DROP FOREIGN KEY `table_name_ibfk_1`;

Then that should do it!

Regards

John Daisley

Microsoft SQL Server 2005/2008 Database Administrator
Certified MySQL 5 Database Administrator
Certified MySQL 5 Developer
Cognos BI Developer

Telephone: +44 (0)7918 621621
Email: john.daisley@butterflysystems.co.uk

On 22 July 2010 14:26, Victor Subervi wrote:

> On Thu, Jul 22, 2010 at 8:47 AM, John Daisley > > wrote:
>
>> Most likely a foreign key constraint would be violated if the table were
>> dropped. Check those index definitions on `Store` and `User` columns.
>>
>
> But I don't want to drop the table, I want to drop the foreign key on
> Store. How?
> TIA,
> V
>
>>
>> Regards
>>
>> John Daisley
>>
>> Microsoft SQL Server 2005/2008 Database Administrator
>> Certified MySQL 5 Database Administrator
>> Certified MySQL 5 Developer
>> Cognos BI Developer
>>
>> Telephone: +44 (0)7918 621621
>> Email: john.daisley@butterflysystems.co.uk
>>
>> On 22 July 2010 14:02, Victor Subervi wrote:
>>
>>> Hi;
>>>
>>> mysql> alter table personalData drop foreign key Store;
>>> ERROR 1025 (HY000): Error on rename of './test/personalData' to
>>> './test/#sql2-14ce-a61' (errno: 152)
>>> mysql> describe personalData;
>>>
>>> +-------------------+------------------+------+-----+------- -----+----------------+
>>> | Field | Type | Null | Key | Default |
>>> Extra |
>>>
>>> +-------------------+------------------+------+-----+------- -----+----------------+
>>> | ID | int(10) unsigned | NO | PRI | NULL |
>>> auto_increment |
>>> | Store | varchar(40) | NO | MUL | NULL
>>> | |
>>> | User | varchar(50) | NO | MUL | NULL
>>> | |
>>> | FirstName | varchar(100) | NO | | NULL
>>> | |
>>> | LastName | varchar(100) | NO | | NULL
>>> | |
>>> | Phone | varchar(13) | YES | | NULL
>>> | |
>>> | Cell | varchar(13) | YES | | NULL
>>> | |
>>> | Fax | varchar(13) | YES | | NULL
>>> | |
>>> | AddressID | int(11) | YES | | NULL
>>> | |
>>> | ShippingAddressID | int(11) | YES | | NULL
>>> | |
>>> | DOB | date | YES | | 2000-01-01
>>> | |
>>> | Email | varchar(100) | NO | | NULL
>>> | |
>>> | PW | varchar(12) | NO | | NULL
>>> | |
>>>
>>> +-------------------+------------------+------+-----+------- -----+----------------+
>>> 13 rows in set (0.00 sec)
>>>
>>> How drop it?
>>> TIA,
>>> Victor
>>>
>>
>>
>>
>> --
>> John Daisley
>>
>> Certified MySQL 5 Database Administrator
>> Certified MySQL 5 Developer
>> Cognos BI Developer
>>
>> Telephone: +44 (0)7918 621621
>> Email: john.daisley@butterflysystems.co.uk
>>
>
>

--
John Daisley

Certified MySQL 5 Database Administrator
Certified MySQL 5 Developer
Cognos BI Developer

Telephone: +44 (0)7918 621621
Email: john.daisley@butterflysystems.co.uk

--00221546ed66bd4475048bfa0c3b--

Re: Why Does This Drop Fail?

am 22.07.2010 15:50:30 von Victor Subervi

--0016367b60a69e2c78048bfa33bc
Content-Type: text/plain; charset=ISO-8859-1

On Thu, Jul 22, 2010 at 9:09 AM, John Daisley wrote:

> Sorry, my bad! Must learn to read the whole message!!
>
>
> This can be caused because when a foreign key is created mysql adds an
> index key to the column in addition to the foreign key. Why I'm not sure,
> but I'm guessing its for performance. To drop this foreign key
>
> First do this to get the index names
>
>
> SHOW CREATE TABLE `table_name`
>
> Which will give an output something like this
>
>
> | table_name | CREATE TABLE `table_name` (
> `id` int(10) unsigned NOT NULL auto_increment,
> `idx` int(5) unsigned NOT NULL,
> KEY `key_column` (`idx`),
> CONSTRAINT `table_name_ibfk_1` FOREIGN KEY (`idx`) REFERENCES
> `second_table` (`id`) ON DELETE SET NULL
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
>
>
> So, first you remove the key:
>
>
> ALTER TABLE table_name DROP KEY `idx`;
>
>
> then the foreign key:
>
>
> ALTER TABLE table_name DROP FOREIGN KEY `table_name_ibfk_1`;
>
>
> Then that should do it!
>

Thank you!
V

--0016367b60a69e2c78048bfa33bc--