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