修改教务系统主键，删除id设置为student

取消自增
ALTER TABLE school_census_schoolcensus MODIFY COLUMN id int NOT NULL;
查看约束名
select TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where REFERENCED_TABLE_NAME = ‘school_census_schoolcensus’;
删除所有外h键约束（通过约束名字）
alter table traininglink_qualificationverify drop foreign key traininglink_qualifi_census_id_19037582_fk_school_ce;
alter table traininglink_report drop foreign key traininglink_report_census_id_34290170_fk_school_ce;
alter table traininglink_phdpaperschedule drop foreign key census_id_refs_id_a901da91;
alter table traininglink_phdexpertgroup drop foreign key census_id_refs_id_d9eab96f;
alter table traininglink_phddissertationschedule drop foreign key school_census_id_refs_id_a45f91b2;
alter table traininglink_innovationprogram drop foreign key traininglink_innovat_student_id_25159daf_fk_school_ce;
alter table traininglink_graduateverify drop foreign key school_census_id_refs_id_b116460b;
alter table score_studentscoreinfo drop foreign key student_id_refs_id_9850f1f1;
alter table score_multipleaverage drop foreign key student_id_refs_id_73c7334f;
alter table school_census_subsidiesrecord drop foreign key sch_student_id_1c29e36c8c70fff6_fk_school_census_schoolcensus_id;
alter table school_census_stuapply drop foreign key user_id_refs_id_c16fb18;
alter table school_census_schoolcensusregister drop foreign key sch_student_id_41f9438a2ba26661_fk_school_census_schoolcensus_id;
alter table school_census_scholarshiprecord drop foreign key sch_student_id_351eb9fd90ad43cb_fk_school_census_schoolcensus_id;
alter table school_census_remuneration drop foreign key school_census_remune_census_id_c50c88d0_fk_school_ce;
alter table course_selection_courseselection drop foreign key school_census_id_refs_id_39272b1b;
mysql> alter table course_selection_class drop foreign key student_id_refs_id_ce18c85e;
alter table traininglink_phdpaper drop foreign key trai_census_id_689a25d71bc4f88f_fk_school_census_schoolcensus_id;
取消主键id
alter table school_census_schoolcensus drop primary key;
增加主键number
alter table school_census_schoolcensus add primary key(number);
 
修改原子表原外键名字为*_blank，给每一个外键表增加一列新的外键(类型为插入类型，名字与原外键名相同)，并赋值为父表新主键number的值，最后删除*_blank
***黄色为外键，绿色为onetoone
表 ：traininglink_phdpaperschedule
 
步骤：
1)alter table traininglink_phdpaperschedule CHANGE census_id census_id_blank int(11);
2)alter table traininglink_phdpaperschedule add column census_id VARCHAR(12) DEFAULT NULL;
3) alter table traininglink_phdpaperschedule add constraint schoolcensus_traininglink_phdpaperschedule foreign key(census_id) REFERENCES school_census_schoolcensus(number) on delete set null;
4)update traininglink_phdpaperschedule,school_census_schoolcensus set traininglink_phdpaperschedule.census_id = school_census_schoolcensus.number where traininglink_phdpaperschedule.census_id_blank = school_census_schoolcensus.id;
5)alter table traininglink_phdpaperschedule drop column census_id_blank;
 
表traininglink_report
1)alter table traininglink_report CHANGE census_id census_id_blank int(11);
2)alter table traininglink_report add column census_id VARCHAR(12) DEFAULT NULL;
3)alter table traininglink_report add constraint schoolcensus_traininglink_report foreign key(census_id) REFERENCES school_census_schoolcensus(number) on delete set null;
4)update traininglink_report,school_census_schoolcensus set traininglink_report.census_id = school_census_schoolcensus.number where traininglink_report.census_id_blank = school_census_schoolcensus.id;
5)alter table traininglink_report drop column census_id_blank;
 
traininglink_qualificationverify
1)alter table traininglink_qualificationverify CHANGE census_id census_id_blank int(11);
2)alter table traininglink_qualificationverify add column census_id VARCHAR(12) DEFAULT NULL;
3)alter table traininglink_qualificationverify add constraint schoolcensus_traininglink_qualificationverify foreign key(census_id) REFERENCES school_census_schoolcensus(number) ;
4)
5)
6)update traininglink_qualificationverify,school_census_schoolcensus set traininglink_qualificationverify.census_id = school_census_schoolcensus.number where traininglink_qualificationverify.census_id_blank = school_census_schoolcensus.id;
7)alter table traininglink_qualificationverify add unique(census_id);
8)alter table traininglink_qualificationverify drop column census_id_blank;
 
 
traininglink_phdpaper
1)alter table traininglink_phdpaper CHANGE census_id census_id_blank int(11);
2)alter table traininglink_phdpaper add column census_id VARCHAR(12) DEFAULT NULL;
3)alter table traininglink_phdpaper add constraint schoolcensus_traininglink_phdpaper foreign key(census_id) REFERENCES school_census_schoolcensus(number) on delete set null;
4)alter table traininglink_phdpaper add unique(census_id);
5)update traininglink_phdpaper,school_census_schoolcensus set traininglink_phdpaper.census_id = school_census_schoolcensus.number where traininglink_phdpaper.census_id_blank = school_census_schoolcensus.id;
6)alter table traininglink_phdpaper drop column census_id_blank;
 
 
traininglink_phdexpertgroup
1)alter table traininglink_phdexpertgroup CHANGE census_id census_id_blank int(11);
 
2)alter table traininglink_phdexpertgroup add column census_id VARCHAR(12) DEFAULT NULL;
3)alter table traininglink_phdexpertgroup add constraint schoolcensus_traininglink_phdexpertgroup foreign key(census_id) REFERENCES school_census_schoolcensus(number) on delete set null;
4)update traininglink_phdexpertgroup,school_census_schoolcensus set traininglink_phdexpertgroup.census_id = school_census_schoolcensus.number where traininglink_phdexpertgroup.census_id_blank = school_census_schoolcensus.id;
5)alter table traininglink_phdexpertgroup drop column census_id_blank;
 
traininglink_phddissertationschedule
1)alter table traininglink_phddissertationschedule CHANGE school_census_id school_census_id_blank int(11);
 
2)alter table traininglink_phddissertationschedule add column school_census_id VARCHAR(12) DEFAULT NULL;
3)alter table traininglink_phddissertationschedule add constraint schoolcensus_traininglink_phddissertationschedule foreign key( school_census_id) REFERENCES school_census_schoolcensus(number) on delete set null;
4)update traininglink_phddissertationschedule,school_census_schoolcensus set traininglink_phddissertationschedule. school_census_id = school_census_schoolcensus.number where traininglink_phddissertationschedule. school_census_id_blank = school_census_schoolcensus.id;
5)alter table traininglink_phddissertationschedule drop column school_census_id_blank;
 
traininglink_innovationprogram
1)alter table traininglink_innovationprogram CHANGE student_id student_id_blank int(11); 
2)alter table traininglink_innovationprogram add column student_id VARCHAR(12) DEFAULT NULL;
3)alter table traininglink_innovationprogram add constraint schoolcensus_traininglink_innovationprogram foreign key( student_id) REFERENCES school_census_schoolcensus(number) on delete set null;
4)update traininglink_innovationprogram,school_census_schoolcensus set traininglink_innovationprogram. student_id = school_census_schoolcensus.number where traininglink_innovationprogram. student_id_blank = school_census_schoolcensus.id;
5)alter table traininglink_innovationprogram drop column student_id_blank;
 
 
traininglink_graduateverify
1)alter table traininglink_graduateverify CHANGE school_census_id school_census_id_blank int(11);
 
2)alter table traininglink_graduateverify add column school_census_id VARCHAR(12) DEFAULT NULL;
3)alter table traininglink_graduateverify add constraint schoolcensus_traininglink_graduateverify foreign key( school_census_id) REFERENCES school_census_schoolcensus(number) ;
4)alter table traininglink_graduateverify add unique(school_census_id);
5)update traininglink_graduateverify,school_census_schoolcensus set traininglink_graduateverify. school_census_id = school_census_schoolcensus.number where traininglink_graduateverify. school_census_id_blank = school_census_schoolcensus.id;
6)alter table traininglink_graduateverify drop column school_census_id_blank;
 
score_studentscoreinfo
1)alter table score_studentscoreinfo CHANGE student_id student_id_blank int(11);
2)alter table score_studentscoreinfo add column student_id VARCHAR(12) DEFAULT NULL;
3)alter table score_studentscoreinfo add constraint schoolcensus_score_studentscoreinfo foreign key( student_id) REFERENCES school_census_schoolcensus(number) ;
4)alter table score_studentscoreinfo add unique(student_id);
5)update score_studentscoreinfo,school_census_schoolcensus set score_studentscoreinfo. student_id = school_census_schoolcensus.number where score_studentscoreinfo. student_id_blank = school_census_schoolcensus.id;
6)alter table score_studentscoreinfo drop column student_id_blank;
 
score_multipleaverage
1)alter table score_multipleaverage CHANGE student_id student_id_blank int(11);
2)alter table score_multipleaverage add column student_id VARCHAR(12) DEFAULT NULL;
3)alter table score_multipleaverage add constraint schoolcensus_score_multipleaverage foreign key( student_id) REFERENCES school_census_schoolcensus(number) on delete set null;
4)update score_multipleaverage,school_census_schoolcensus set score_multipleaverage. student_id = school_census_schoolcensus.number where score_multipleaverage. student_id_blank = school_census_schoolcensus.id;

alter table score_multipleaverage drop column student_id_blank;

ERROR 1062 (23000): Duplicate entry ‘7870’ for key ‘student_id’
school_census_subsidiesrecord
1)alter table school_census_subsidiesrecord CHANGE student_id student_id_blank int(11); 
2)alter table school_census_subsidiesrecord add column student_id VARCHAR(12) DEFAULT NULL;
3)alter table school_census_subsidiesrecord add constraint schoolcensus_school_census_subsidiesrecord foreign key( student_id) REFERENCES school_census_schoolcensus(number);
4)update school_census_subsidiesrecord,school_census_schoolcensus set school_census_subsidiesrecord. student_id = school_census_schoolcensus.number where school_census_subsidiesrecord. student_id_blank = school_census_schoolcensus.id;
5)alter table school_census_subsidiesrecord drop column student_id_blank;
 
school_census_stuapply
1)alter table school_census_stuapply CHANGE user_id user_id_blank int(11);
2)alter table school_census_stuapply add column user_id VARCHAR(12) DEFAULT NULL;
3)alter table school_census_stuapply add constraint schoolcensus_school_census_stuapply foreign key(user_id) REFERENCES school_census_schoolcensus(number);
4)update school_census_stuapply ,school_census_schoolcensus set school_census_stuapply . user_id = school_census_schoolcensus.number where school_census_stuapply . user_id_blank = school_census_schoolcensus.id;
5)alter table school_census_stuapply add unique(user_id);
6)alter table school_census_stuapply drop column user_id_blank;
 
school_census_schoolcensusregister
1)alter table school_census_schoolcensusregister CHANGE student_id student_id_blank int(11); 
2)alter table school_census_schoolcensusregister add column student_id VARCHAR(12) DEFAULT NULL;
3)alter table school_census_schoolcensusregister add constraint schoolcensus_school_census_schoolcensusregister foreign key( student_id) REFERENCES school_census_schoolcensus(number);
4)update school_census_schoolcensusregister,school_census_schoolcensus set school_census_schoolcensusregister.student_id = school_census_schoolcensus.number where school_census_schoolcensusregister.student_id_blank = school_census_schoolcensus.id;
 alter table school_census_schoolcensusregister drop column student_id_blank;
ERROR 1062 (23000): Duplicate entry ‘2016-2017-1’ for key ‘school_census_schoolcensusregister_term_774ca6c7502bfe0c_uniq’
school_census_scholarshiprecord
1)alter table school_census_scholarshiprecord CHANGE student_id student_id_blank int(11); 
2)alter table school_census_scholarshiprecord add column student_id VARCHAR(12) DEFAULT NULL;
3)alter table school_census_scholarshiprecord add constraint schoolcensus_school_census_scholarshiprecord foreign key( student_id) REFERENCES school_census_schoolcensus(number) ;

4)update school_census_scholarshiprecord,school_census_schoolcensus set school_census_scholarshiprecord.student_id = school_census_schoolcensus.number where school_census_scholarshiprecord.student_id_blank = school_census_schoolcensus.id;
5)alter table school_census_scholarshiprecord drop column student_id_blank;

school_census_remuneration
1)alter table school_census_remuneration CHANGE census_id census_id_blank int(11); 
2)alter table school_census_remuneration add column census_id VARCHAR(12) DEFAULT NULL;
3)alter table school_census_remuneration add constraint schoolcensus_school_census_remuneration foreign key(census_id) REFERENCES school_census_schoolcensus(number);
4)update school_census_remuneration,school_census_schoolcensus set school_census_remuneration.census_id = school_census_schoolcensus.number where school_census_remuneration.census_id_blank = school_census_schoolcensus.id;
5)alter table school_census_remuneration drop column census_id_blank;
 
course_selection_courseselection
1)alter table course_selection_courseselection CHANGE school_census_id school_census_id_blank int(11);
2)alter table course_selection_courseselection add column school_census_id VARCHAR(12) DEFAULT NULL;
3)alter table course_selection_courseselection add constraint schoolcensus_course_selection_courseselection foreign key( school_census_id) REFERENCES school_census_schoolcensus(number) on delete set null;
4)update course_selection_courseselection,school_census_schoolcensus set course_selection_courseselection. school_census_id = school_census_schoolcensus.number where course_selection_courseselection. school_census_id_blank = school_census_schoolcensus.id;
cd

course_selection_class
1)alter table course_selection_class CHANGE student_id student_id_blank int(11);
2)alter table course_selection_class add column student_id VARCHAR(12) DEFAULT NULL;
3)alter table course_selection_class add constraint schoolcensus_course_selection_class foreign key( student_id) REFERENCES school_census_schoolcensus(number) on delete set null;
4)update course_selection_class,school_census_schoolcensus set course_selection_class.student_id = school_census_schoolcensus.number where course_selection_class.student_id_blank = school_census_schoolcensus.id;
5)alter table course_selection_class drop column student_id_blank;
 
school_census_pregraduatemaster
1)alter table school_census_pregraduatemaster CHANGE student_id student_id_blank int(11); 
2)alter table school_census_pregraduatemaster add column student_id VARCHAR(12) DEFAULT NULL;
3)alter table school_census_pregraduatemaster add constraint schoolcensus_school_census_pregraduatemasters foreign key( student_id) REFERENCES school_census_schoolcensus(number);
4)update school_census_pregraduatemaster,school_census_schoolcensus set school_census_pregraduatemaster.student_id = school_census_schoolcensus.number where school_census_pregraduatemaster.student_id_blank = school_census_schoolcensus.id;

5)alter table school_census_pregraduatemaster add unique(student_id);
6)alter table school_census_pregraduatemaster drop column student_id_blank;
score_courseselection
school_census_schoolcensus.id;
5)alter table score_courseselection change school_census_id school_census_id_blank int(11);
6)alter table score_courseselection add column school_census_id VARCHAR(12) DEFAULT NULL;
7)alter table score_courseselection add constraint schoolcensus_score_courseselection foreign key( school_census_id) REFERENCES school_census_schoolcensus(number) on delete set null;
8)update score_courseselection,school_census_schoolcensus set score_courseselection. school_census_id = school_census_schoolcensus.number where score_courseselection. school_census_id_blank = school_census_schoolcensus.id;

traininglink_phdpaper | census_id | trai_census_id_689a25d71bc4f88f_fk_school_census_schoolcensus_id | school_census_schoolcensus | id |
| course_selection_class | student_id | student_id_refs_id_ce18c85e | school_census_schoolcensus | id |
| course_selection_courseselection | school_census_id | school_census_id_refs_id_39272b1b | school_census_schoolcensus | id |
| school_census_remuneration | census_id | schoo_census_id_b18cdee28fa7015_fk_school_census_schoolcensus_id | school_census_schoolcensus | id |
| school_census_scholarshiprecord | student_id | sch_student_id_351eb9fd90ad43cb_fk_school_census_schoolcensus_id | school_census_schoolcensus | id |
| school_census_schoolcensusregister | student_id | sch_student_id_41f9438a2ba26661_fk_school_census_schoolcensus_id | school_census_schoolcensus | id |
| school_census_stuapply | user_id | user_id_refs_id_c16fb18 | school_census_schoolcensus | id |
| school_census_subsidiesrecord | student_id | sch_student_id_1c29e36c8c70fff6_fk_school_census_schoolcensus_id |