1 -- Modify tables that are refered to.
2 ALTER TABLE profile_education_enum
MODIFY COLUMN id INT(11) UNSIGNED
NOT NULL AUTO_INCREMENT
;
3 ALTER TABLE profile_education_degree_enum
MODIFY COLUMN id INT(11) UNSIGNED
NOT NULL AUTO_INCREMENT
;
4 ALTER TABLE profile_education_field_enum
MODIFY COLUMN id INT(11) UNSIGNED
NOT NULL AUTO_INCREMENT
;
5 ALTER TABLE profile_section_enum
MODIFY COLUMN id TINYINT(2) UNSIGNED
NOT NULL DEFAULT 0;
7 -- Following tables all needs a sligth change in their definition to match their foreign correspondance.
8 ALTER TABLE profile_addresses
MODIFY COLUMN localityId
BIGINT(20) UNSIGNED
DEFAULT NULL;
9 ALTER TABLE profile_addresses
MODIFY COLUMN subAdministrativeAreaId
INT(11) UNSIGNED
DEFAULT NULL;
10 ALTER TABLE profile_addresses
MODIFY COLUMN administrativeAreaId
INT(11) UNSIGNED
DEFAULT NULL;
11 ALTER TABLE profile_binets
MODIFY COLUMN binet_id
TINYINT(3) UNSIGNED
DEFAULT NULL;
12 ALTER TABLE profile_education
MODIFY COLUMN eduid
INT(11) UNSIGNED
DEFAULT NULL;
13 ALTER TABLE profile_education
MODIFY COLUMN degreeid
INT(11) UNSIGNED
DEFAULT NULL;
14 ALTER TABLE profile_education
MODIFY COLUMN fieldid
INT(11) UNSIGNED
DEFAULT NULL;
15 ALTER TABLE profile_education_enum
MODIFY COLUMN country
CHAR(2) DEFAULT 'FR';
16 ALTER TABLE profile_education_degree
MODIFY COLUMN eduid
INT(11) UNSIGNED
DEFAULT 0;
17 ALTER TABLE profile_education_degree
MODIFY COLUMN degreeid
INT(11) UNSIGNED
DEFAULT 0;
18 ALTER TABLE profile_job
MODIFY COLUMN jobid
INT(6) UNSIGNED
DEFAULT NULL;
20 -- Prepares table having ids set to 0.
21 DELETE FROM profile_mentor_country
WHERE country
= 'YU' OR country
= '00';
22 UPDATE profile_addresses
SET localityId
= NULL WHERE localityId
= 0;
23 UPDATE profile_addresses
SET subAdministrativeAreaId
= NULL WHERE subAdministrativeAreaId
= 0;
24 UPDATE profile_addresses
SET administrativeAreaId
= NULL WHERE administrativeAreaId
= 0;
25 UPDATE profile_addresses
SET countryId
= NULL WHERE countryId
= '';
26 UPDATE profile_education
SET eduid
= NULL WHERE eduid
= 0;
27 UPDATE profile_education
SET degreeid
= NULL WHERE degreeid
= 0;
28 UPDATE profile_education
SET fieldid
= NULL WHERE fieldid
= 0;
29 UPDATE profile_education_enum
SET country
= NULL WHERE country
= '';
30 UPDATE profile_job
SET jobid
= NULL WHERE jobid
= 0;
31 UPDATE profile_job_enum
SET holdingid
= NULL WHERE holdingid
= 0;
32 UPDATE profiles
SET section = NULL WHERE section = 0;
34 -- Adds missing data in foreign tables.
35 INSERT INTO geoloc_countries (iso_3166_1_a2
, iso_3166_1_a3
, iso_3166_1_num
, worldRegion
, country
,
36 countryFR
, capital
, nationalityFR
, licensePlate
, belongsTo
, phonePrefix
)
37 VALUES ('GF', 'GUF', 254, 'EU', 'French Guiana', 'Guyane française', 'Cayenne', NULL, 'FR', 'FR', 594),
38 ('GP', 'GLP', 312, 'EU', 'Guadeloupe', 'Guadeloupe', 'Basse-Terre', NULL, 'FR', 'FR', 590),
39 ('MQ', 'MTQ', 474, 'EU', 'Martinique', 'Martinique', 'Fort-de-France', NULL, 'FR', 'FR', 596),
40 ('NC', 'NCL', 540, 'EU', 'New Caledonia', 'Nouvelle-Calédonie', 'Nouméa', NULL, 'FR', 'FR', 687),
41 ('PF', 'PYF', 258, 'EU', 'French Polynesia', 'Polynésie française', 'Papeete', NULL, 'FR', 'FR', 689),
42 ('PR', 'PRI', 630, 'NA', 'Puerto Rico', 'Porto Rico', 'San Juan', NULL, 'USA', 'US', 1787),
43 ('MP', 'MNP', 580, 'NA', 'Northern Mariana Islands', 'Îles Mariannes du Nord', ' Saipan', NULL, 'USA', 'US', 1670);
45 -- Finaly we add the foreign keys.
46 ALTER TABLE profile_addresses
ADD FOREIGN KEY (localityId
) REFERENCES geoloc_localities (id) ON DELETE CASCADE ON UPDATE CASCADE;
47 ALTER TABLE profile_addresses
ADD FOREIGN KEY (subAdministrativeAreaId
) REFERENCES geoloc_subadministrativeareas (id) ON DELETE CASCADE ON UPDATE CASCADE;
48 ALTER TABLE profile_addresses
ADD FOREIGN KEY (administrativeAreaId
) REFERENCES geoloc_administrativeareas (id) ON DELETE CASCADE ON UPDATE CASCADE;
49 ALTER TABLE profile_addresses
ADD FOREIGN KEY (countryId
) REFERENCES geoloc_countries (iso_3166_1_a2
) ON DELETE CASCADE ON UPDATE CASCADE;
50 ALTER TABLE profile_binets
ADD FOREIGN KEY (binet_id
) REFERENCES profile_binet_enum (id) ON DELETE CASCADE ON UPDATE CASCADE;
51 ALTER TABLE profile_corps
ADD FOREIGN KEY (original_corpsid
) REFERENCES profile_corps_enum (id) ON DELETE CASCADE ON UPDATE CASCADE;
52 ALTER TABLE profile_corps
ADD FOREIGN KEY (current_corpsid
) REFERENCES profile_corps_enum (id) ON DELETE CASCADE ON UPDATE CASCADE;
53 ALTER TABLE profile_corps
ADD FOREIGN KEY (rankid
) REFERENCES profile_corps_rank_enum (id) ON DELETE CASCADE ON UPDATE CASCADE;
54 ALTER TABLE profile_education
ADD FOREIGN KEY (eduid
) REFERENCES profile_education_enum (id) ON DELETE CASCADE ON UPDATE CASCADE;
55 ALTER TABLE profile_education
ADD FOREIGN KEY (degreeid
) REFERENCES profile_education_degree_enum (id) ON DELETE CASCADE ON UPDATE CASCADE;
56 ALTER TABLE profile_education
ADD FOREIGN KEY (fieldid
) REFERENCES profile_education_field_enum (id) ON DELETE CASCADE ON UPDATE CASCADE;
57 ALTER TABLE profile_education_enum
ADD FOREIGN KEY (country
) REFERENCES geoloc_countries (iso_3166_1_a2
) ON DELETE CASCADE ON UPDATE CASCADE;
58 ALTER TABLE profile_education_degree
ADD FOREIGN KEY (eduid
) REFERENCES profile_education_enum (id) ON DELETE CASCADE ON UPDATE CASCADE;
59 ALTER TABLE profile_education_degree
ADD FOREIGN KEY (degreeid
) REFERENCES profile_education_degree_enum (id) ON DELETE CASCADE ON UPDATE CASCADE;
60 ALTER TABLE profile_job
ADD FOREIGN KEY (jobid
) REFERENCES profile_job_enum (id) ON DELETE CASCADE ON UPDATE CASCADE;
61 ALTER TABLE profile_job_enum
ADD FOREIGN KEY (holdingid
) REFERENCES profile_job_enum (id) ON DELETE CASCADE ON UPDATE CASCADE;
62 ALTER TABLE profile_langskills
ADD FOREIGN KEY (lid
) REFERENCES profile_langskill_enum (id) ON DELETE CASCADE ON UPDATE CASCADE;
63 ALTER TABLE profile_medal_grade_enum
ADD FOREIGN KEY (mid
) REFERENCES profile_medal_enum (id) ON DELETE CASCADE ON UPDATE CASCADE;
64 ALTER TABLE profile_mentor_country
ADD FOREIGN KEY (country
) REFERENCES geoloc_countries (iso_3166_1_a2
) ON DELETE CASCADE ON UPDATE CASCADE;
65 ALTER TABLE profile_skills
ADD FOREIGN KEY (cid
) REFERENCES profile_skill_enum (id) ON DELETE CASCADE ON UPDATE CASCADE;
66 ALTER TABLE profile_name
ADD FOREIGN KEY (typeid
) REFERENCES profile_name_enum (id) ON DELETE CASCADE ON UPDATE CASCADE;
67 ALTER TABLE profile_networking
ADD FOREIGN KEY (nwid
) REFERENCES profile_networking_enum (nwid
) ON DELETE CASCADE ON UPDATE CASCADE;
68 ALTER TABLE profiles
ADD FOREIGN KEY (section) REFERENCES profile_section_enum (id) ON DELETE CASCADE ON UPDATE CASCADE;
69 ALTER TABLE profiles
ADD FOREIGN KEY (nationality1
) REFERENCES geoloc_countries (iso_3166_1_a2
) ON DELETE CASCADE ON UPDATE CASCADE;
70 ALTER TABLE profiles
ADD FOREIGN KEY (nationality2
) REFERENCES geoloc_countries (iso_3166_1_a2
) ON DELETE CASCADE ON UPDATE CASCADE;
71 ALTER TABLE profiles
ADD FOREIGN KEY (nationality3
) REFERENCES geoloc_countries (iso_3166_1_a2
) ON DELETE CASCADE ON UPDATE CASCADE;
73 -- vim:set syntax=mysql: