1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

MYSQL data type changes without affecting existing table foreign key costraints

Discussion in 'MySQL' started by nazeeeeeeeer, Jan 18, 2013.

  1. nazeeeeeeeer

    nazeeeeeeeer New Member

    Joined:
    Jan 18, 2013
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    0
    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.
     
    Last edited by a moderator: Jan 18, 2013

Share This Page