Go4Expert

Go4Expert (http://www.go4expert.com/)
-   MySQL (http://www.go4expert.com/forums/mysql-forum/)
-   -   MYSQL data type changes without affecting existing table foreign key costraints (http://www.go4expert.com/forums/mysql-data-type-changes-affecting-t29431/)

nazeeeeeeeer 18Jan2013 17:49

MYSQL data type changes without affecting existing table foreign key costraints
 
hi i have to optimize my database which has 212 table , actutaly one table primary key is acting as foriegnkey for 94 tables.for example i have table
Code:

create table CUSTOMER (
        CUSTOMER_ID BIGINT NOT NULL,
        CUSTOMER_NAME VARCHAR(255) NOT NULL UNIQUE,
        CONTACT_PERSON VARCHAR(255) NOT NULL,
        CUSTOMER_EMAIL VARCHAR(255) NOT NULL,
        ADDRESS VARCHAR(255),
        CITY VARCHAR(255),
        PHONE VARCHAR(255),
        FAX VARCHAR(255),

        constraint CUSTOMER_PK PRIMARY KEY (CUSTOMER_ID)
);

here CUSTOMER_ID is acting as references that is foriegnkey for 94 tables. here one table as for ecxample
Code:

create table CUSTOMER_STATUS (
        CUSTOMER_DETAILS_ID BIGINT NOT NULL,
        CUSTOMER_ID BIGINT NOT NULL,
        USER_ID BIGINT NOT NULL,
        TRIAL_PERIOD BIGINT NOT NULL,
        TRIAL_PERIOD_TYPE BIGINT NOT NULL,
        EXPIRY_DATE BIGINT NOT NULL,
        MAXIMUM_CLIENTS BIGINT NOT NULL,
        STATUS BIGINT NOT NULL,
        AUTO_AUTHORIZATION BIGINT NOT NULL,

        constraint CUSTOMER_STATUS_PK PRIMARY KEY (CUSTOMER_ID,CUSTOMER_DETAILS_ID),
        constraint CUSTOMER_STATUS_FK1 FOREIGN KEY (CUSTOMER_ID) references CUSTOMER(CUSTOMER_ID)
);

i need to change CUSTOMER_ID data type from BIGINT to TINYINT for all tables but its making error because of foriegn key constraints.

error ::

mysql> alter table CUSTOMER MODIFY CUSTOMER_ID TINYINT;
ERROR 1025 (HY000): Error on rename of '.\uds7\#sql-670_cdd' to '.\uds7\customer' (errno: 150)

what should i do to make changes without affecting my table data.give me solution.


All times are GMT +5.5. The time now is 16:40.