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.

 

Hi,

You can try any of the two

ALTER TABLE CUSTOMER ALTER COLUMN CUSTOMER_ID TINYINT;

or

ALTER TABLE CUSTOMER MODIFY COLUMN CUSTOMER_ID TINYINT;

Thanks