mysql - How to modify data type of a column that is foreign key or primary key? -

mysql - How to modify data type of a column that is foreign key or primary key? -

i have 3 tables. 1 course, section , other prerequisite.

create table course( course_number int(11) not null auto_increment, course_name varchar(20) not null, credit_hours int(11) not null, section varchar(5) not null, primary key (course_number) ) create table section ( section_id int(11) not null, course_number` int(11) not null, semester varchar(6) not null, year year(4) not null, instructor varchar(20) default null, primary key (section_id), foreign key (course_number) references course of study (course_number) ) create table prerequisite( course_number int not null auto_increment, prerequisite int not null, primary key (course_number), foreign key (prerequisite) references course(course_number) );

i want alter datatype of course_number in both tables, when run query

alter table course of study alter course_number course_number varchar(20);

i see next error:

cannot alter column 'course_number': used in foreign key constraint 'section_fk' of table 'university.section'

what problem in here? best solution? saying best solution, mean, not loosing info or dropping table , creating scratch.

drop foreign key, alter both tables , add together foreign key. btw thought name constraints explicitly. have constraint name in catalog:

select constraint_name information_schema.referential_constraints table_name = 'prerequisite' , referenced_table_name = 'course' alter table prerequisite drop constraint ...; alter table course of study alter course_number course_number varchar(20); alter table prerequisite alter course_number course_number varchar(20); alter table prerequisite add together constraint <name> foreign key (course_number) references course of study (course_number) <actions>;

mysql sql