I am creatign three tables. The third table needs to reference the primary key of the two other tables. I created three SQL statements, but am unsure if I am on the right track. I would appreciate some feedback on the statements. Any help is greatly appreciated.
The three tables are: Disease(primary key = D_id), Demographics(primary key = City_id), Outbreaks
Outbreaks must reference D_id and City_id.
If we do not want to keep the statistics about a certain disease anymore and we delete the tuple in Disease table, then all
the records for that disease in Outbreaks table should be automatically deleted.
Here is what I have come up with so far:
CREATE TABLE Disease (D_id CHAR(10) D_Name CHAR(20) D_type CHAR(20) Longevity INT(3) Severity INT(2) PRIMARY KEY(D_id) UNIQUE (D_Name)) CREATE TABLE Demographics (City_id VARCHAR(5) Name CHAR(20) Population INT(10) PRIMARY KEY (City_id) UNIQUE (Name)) CREATE TABLE Outbreaks (D_id CHAR(10) City_id VARCHAR(5) N_outbreaks INT(10) FOREIGN KEY(D_id(REFERENCES Disease ON DELETE CASCADE)) FOREIGN KEY (City_id(REFRENCES Demographics ON DELETE CASCADE)))
Once again, any help is greatly appreciated.