1

Topic: Binding of already created tables of a DB

Kind time of days!
The help is necessary!
There are 2 already created tables:

CREATE TABLE Region
(
indexses INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
Region_of_the_World ENUM (' the North America ', ' the South America ', ' Central  ';
' Europe ', ' Asia ', ' Australia ', ' Africa '),
Opisanie VARCHAR (1000)
);
CREATE TABLE Climate
(
indexses INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
Region_of_the_World ENUM (' the North America ', ' the South America ', ' Central  ';
' Europe ', ' Asia ', ' Australia ', ' Africa '),
Country VARCHAR (30),
Locality VARCHAR (30),
Years TIMESTAMP,
Months ENUM (' January ', ' February ', ' March ', ' April ', ' May ', ' June ';
' July ', ' August ', ' September ', ' October ', ' November ', ' December '),
Days INT,
Temperature INT,
Vlagnoste INT,
Direction_of_the_wind VARCHAR (5),
Speed_of_wind INT
);

Now it would be required that at table Climate field Region_of_the_World ENUM was tightened and has been connected to the field of table Region with the same name. In table Region and the description of regions, and names will be stored in table Climate of the country of these regions and the information on them only.
If I correctly understood, communication should be one-to-many of table Region in  Climate. But here to build the given communication at me it is impossible.
To begin with I tried to modify existing field Region_of_the_World ENUM in table Climate that it became foreign key, but it did not turn out. Further I generally decided to try to delete the necessary column, and then to add it again with the necessary parameters:

ALTER TABLE climate DROP Region_of_the_World;
ALTER TABLE climate ADD (CONSTRAINT ' regon-climate ' FOREIGN KEY Climate (' Region_of_the_World ')
REFERENCES ' Region ' (' Region_of_the_World '));

It did not turn out. Anew to create  it is impossible, on a condition laboratory. For descriptive reasons I attach the circuit.
Explain how to modify please already existing field in tables that it became exterior a key.

2

Re: Binding of already created tables of a DB

Vlad __ i __ mir wrote:

Now it would be required that at table Climate field Region_of_the_World ENUM was tightened and has been connected to the field of table Region with the same name. In table Region and the description of regions, and names will be stored in table Climate of the country of these regions and the information on them only.

-- To add a field for communication
ALTER TABLE Climate
ADD COLUMN ID_Region INT NOT NULL DEFAULT 0;
-- To fill a communication field
UPDATE Climate, Region
SET Climate. ID_Region = Region.indexses
WHERE Climate. Region_of_the_World = Region. Region_of_the_World;
-- Check that there are no untied records
SELECT Climate.*
FROM Climate
WHERE ID_Region = 0;
-- If untied records are found out - to correct.
-- To add exterior communication
ALTER TABLE Climate
ADD CONSTRAINT region-climate
FOREIGN KEY (ID_Region)
REFERENCES Region (indexses));
-- To delete an unnecessary field
ALTER TABLE Climate
DROP COLUMN Region_of_the_World;