1

So the problem im having is the foreign keys in my table, when I run without the foreign key is creates the tables. the error im will be posted at the bottom, Just trying to get familiar with SQL.

CREATE TABLE Player
(
Player_ID VARCHAR(20) NOT NULL,
Fname VARCHAR(15),
Sname VARCHAR(15),
DOB    NUMBER(3),
Height NUMBER(3),
Weight NUMBER(3),
Position VARCHAR(15),
Team_ID VARCHAR(20) NOT NULL,

CONSTRAINT Player_primary_key PRIMARY KEY (Player_ID),
CONSTRAINT Player_foreign_key FOREIGN KEY(Team_ID) REFERENCES TEAM(Team_ID)
);


CREATE TABLE Team
(
Team_ID VARCHAR(20) NOT NULL,
Tname VARCHAR(20),
Tlocation VARCHAR(20),
Coach VARCHAR(20),
Gameswon NUMBER(10),
Gameslost NUMBER (10),

CONSTRAINT Team_primary_key PRIMARY KEY (Team_ID)
);


CREATE TABLE Match
(
Match_ID VARCHAR(20) NOT NULL,
Hometeam VARCHAR(20), 
Awayteam VARCHAR(20), 
Score NUMBER (10),
Mdate NUMBER(10),
Mtime NUMBER(10),

CONSTRAINT Match_primary_key PRIMARY KEY (Match_ID),
CONSTRAINT Match_foreign_keyHome FOREIGN KEY (Team_ID) REFERENCES HOST(Team_ID),
CONSTRAINT Match_foreign_keyAway FOREIGN KEY (Team_ID) REFERENCES TEAM(Team_ID)
);

CREATE TABLE HOST
(
Team_ID VARCHAR(20) NOT NULL,
Match_ID VARCHAR(20) NOT NULL,

CONSTRAINT Host_Foriegn_Key_Team FOREIGN KEY (Team_ID) REFERENCES TEAM(Team_ID),
CONSTRAINT Host_Foriegn_Key_Match FOREIGN KEY (Match_ID) REFERENCES MATCH(Match_ID)
);

1 Answers1

1

Well your first error seems to be related to the fact that you have not defined the Team table yet. Since you are using:

CONSTRAINT Player_foreign_key FOREIGN KEY(Team_ID) REFERENCES TEAM(Team_ID)

For the Match table you are creating a foreign key with Team_ID even though it does not exist in your table. That's why it's throwing the error:

CONSTRAINT Match_foreign_keyAway FOREIGN KEY (Team_ID) REFERENCES TEAM(Team_ID)

And for your last error MATCH is actually a SQL command which is why it turns blue when you put it as a code sample. I'm thinking this is why it's looking for a table name but assuming you're not providing one. That's just my guess for the last error.

1) So I would first create your Team table instead of creating Player first.
2) Then I would probably change Team_ID for Match_foreign_keyHome to Hometeam:

CONSTRAINT Match_foreign_keyHome FOREIGN KEY (Hometeam) REFERENCES HOST(Team_ID),

And then I would change Team_ID for Match_foreign_keyAway to Awayteam:

CONSTRAINT Match_foreign_keyHome FOREIGN KEY (Awayteam) REFERENCES HOST(Team_ID),
Dresden
  • 549
  • 2
  • 13
  • Thanks very much for that, made a few silly mistakes with match which you pointed out, very new to this so thanks again, also sorry about the layout will format next time. –  Mar 31 '16 at 19:27
  • The tables created which was good just going to test with data tomorrow to check its all working correctly. –  Mar 31 '16 at 19:52
  • So you were able to create all of your tables without any issue? That's good to hear. Did you rename your Match table or how did you fix that issue you were encountering? – Dresden Mar 31 '16 at 20:38
  • I changed match to game, there's a thing thats got me scratching my head though, so when a game occurs theres an away hometeam(at there grounds) and the awayteam, should i make the keys NUMBER or VARCHAR? –  Apr 01 '16 at 20:39
  • It's always better to use integers as your keys because they do not have the ability to contain white space. When you use a VARCHAR it's possible to get white space in your keys which can create a lot of frustration down the road: [See example](http://stackoverflow.com/questions/2103322/varchar-as-foreign-key-primary-key-in-database-good-or-bad). You're already going to have your Team name in TName as a VARCHAR so it's easy to just use selects that pull the TName instead of just having the Team_ID displayed. Then when you insert a match you can put the correct Team_ID for home and away. – Dresden Apr 01 '16 at 22:59