Gordon wrote:
>Here is the table structure.
>In this case I tried to change the 4 characters in prod_ID when I get the
>message, but I get the same message when I try to make any changes.
>
>Doing the exact same activity works fine on all of the 3.23/4.0/4.1 versions
>I have installed previouly. Tables with unsigned integer fields work just
>fine through this interface on previous versions.
>
unsigned int doesn't work for me. Anyway, you don't seem to have an
unsigned in in the table def below so it doesn't matter.
> The only anomaly up to
>this point is that I can't change timestamp fields through the odbc
>connection. They display as dates in access and I can change other fields in
>the table correctly, just can't change the timestamp fields.
>
>
Timestamp fields aren't for you to change. MySQL changes them when you
edit the record. If you want to edit a timestamp field, what you really
need is another field, of type datetime ... you can edit those to your
heart's content.
>mysql> show create table product_order_choice;
>---------------------------+
>| Table | Create Table
>
>|
>---------------------------+
>| product_order_choice | CREATE TABLE `product_order_choice` (
> `cpny_ID` varchar(4) NOT NULL default '',
> `prod_ID` varchar(4) NOT NULL default '',
> `porc_Look_Up_Type` varchar(25) NOT NULL default '',
> `prft_Sub_Month` char(2) NOT NULL default '00',
> `prft_Sub_Item` char(2) NOT NULL default '0',
> `poch_Name` varchar(100) NOT NULL default '',
> `prct_ID` varchar(4) default NULL,
> `poch_Value` text,
> `poch_Image_Path` varchar(255) default NULL,
> `poch_Link` varchar(255) default NULL,
> `poch_Link_2` varchar(255) default NULL,
> `poch_Active` enum('Yes','No','Hidden') NOT NULL default 'Yes',
> `poch_Timestamp` timestamp(14) NOT NULL,
> `poch_Create` datetime NOT NULL default '0000-00-00 00:00:00',
> PRIMARY KEY
>(`cpny_ID`,`prod_ID`,`porc_Look_Up_Type`,`prft_Sub_Month`,`prft_Sub_Item`,`p
>och_Name`)
>) TYPE=InnoDB |
>---------------------------+
>1 row in set (0.00 sec)
>
>
>
I would strongly recommend against using a composite primary key. This
could be the cause of your error. If you want you can still put a unique
key across all 4 fields, which has the same effect of making sure you
don't get duplicates across them. I would use an unsigned mediumint
auto_increment field as the primary key, and then put your unique index
across the 4 fields that are now your primary key. You will of course
have to re-link your tables in Access after this. You shouldn't need to
rewrite any code immediately after this - you can still link to this
table via the composite index, but you will probably get better
performance by rewriting any joins to use the auto_increment primary key.
--
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: [EMAIL PROTECTED]
website: http://www.nusconsulting.com.au
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
