CODEWITHSUNDEEP
mysqlsql
user1987482
user1987482

Reputation: 31

changing data type from bigint to tinyint form existing table with foreign key reference

I have to optimize my database which has 212 tables. One table primary key is acting as foreign key for 94 tables. For example I have this table:

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 a reference that is a foreign key for 94 tables. Here is one table as an example:

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 I'm getting an error because of foreign 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?

Upvotes: 1

Views: 1704

Answers (2)

eggyal
eggyal

Reputation: 125835

You can disable foreign key constraint checks whilst you make schema changes:

SET foreign_key_checks = 0;
ALTER TABLE CUSTOMER        MODIFY CUSTOMER_ID TINYINT;
ALTER TABLE CUSTOMER_STATUS MODIFY CUSTOMER_ID TINYINT;
SET foreign_key_checks = 1;

Note that the data type (including size and sign in the case of integers) of both the referencing and referenced columns must be the same when you reenable foreign key checks.

Upvotes: 0

John Woo
John Woo

Reputation: 263693

You can't do that directly.

The things you will do

  • drop all contraints (foreign key) that references the CUSTOMER table primary key
  • you can now change the data type of the primary key
  • change also the data types of the foreign key same with the data type to which the key will be referenced
  • add foreign key constraints again.

Upvotes: 2

Related Questions