Two foreign keys in one CREATE TABLE statement?

Page 1 of 1

2 Replies - 723 Views - Last Post: 27 February 2010 - 11:00 AM Rate Topic: -----

#1 Guest_Crosby87*


Reputation:

Two foreign keys in one CREATE TABLE statement?

Posted 26 February 2010 - 05:36 PM

Hello,

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.

Is This A Good Question/Topic? 0

Replies To: Two foreign keys in one CREATE TABLE statement?

#2 AdaHacker  Icon User is offline

  • Resident Curmudgeon

Reputation: 433
  • View blog
  • Posts: 789
  • Joined: 17-June 08

Re: Two foreign keys in one CREATE TABLE statement?

Posted 27 February 2010 - 08:18 AM

So, what was your actual question? Are you having trouble with the CREATE TABLE syntax? You've basically got the right idea, you just need to fix your CREATE TABLE statement to be syntactically correct. You've got a lot of missing commas, missing ON UPDATEs in the foreign key declaration (you can specify both or neither, but you can't give just one), and your parentheses are messed up.
CREATE TABLE Outbreaks (
  D_id        CHAR(10),
  City_id     VARCHAR(5),
  N_outbreaks INT(10),
  FOREIGN KEY (D_id) REFERENCES Disease(D_id) ON UPDATE CASCADE ON DELETE CASCADE,
  FOREIGN KEY (City_id) REFERENCES Demographics(City_id) ON UPDATE CASCADE ON DELETE CASCADE
);

Was This Post Helpful? 2
  • +
  • -

#3 Guest_Crosby87*


Reputation:

Re: Two foreign keys in one CREATE TABLE statement?

Posted 27 February 2010 - 11:00 AM

AdaHacker,

Thank you very much. My original question was how to go about creating the CREATE TABLE statement with the two foreign keys. Also, I was unaware that I was missing all of thoe componenets. Thank you.
Was This Post Helpful? 0
Page 1 of 1