cannot make a table with two foreign keys

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

cannot make a table with two foreign keys

Author

Topic

finlayhards
Starting Member

1 Post

Posted - 2011-03-04 : 05:17:13

Hi, for my A level computing project i'm creating a sweepstake management system. Here is a list of the tables and values i am using in the database:

Players League
Player Name (foreign key)
Player Daily Score
Player Total Score
Player Rank

Players
Player ID (Primary Key)
Player Name

Game Fixtures
Fixture ID (primary key)
Fixture Date

Game Results
Fixture ID (foreign key)
Result ID (primary key)
Result Date

Predictions****
Player ID (foreign key)
Player Predictions
Fixture ID (foreign key)

Scores
Player Daily Score
Player Total Score

****
the predictions table is the table i am having the issue with. The table needs to be able to read the playerID from the Players table and also the FixtureID from the fixtures table

Any help would be appreciated

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-03-04 : 09:11:25

So what is the issue you're having?

Another thing: the Player Name in the Players League-table should not be a foreign key, you should use the PlayerID instead.

- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-03-04 : 12:55:55

[code]ALTER TABLE Predictions
ADD CONSTRAINT <constraint name> FOREIGN KEY ([Player ID]) REFERENCES Players([Player ID])
go

ALTER TABLE Predictions
ADD CONSTRAINT <constraint name> FOREIGN KEY ([Fixture ID]) REFERENCES [Game Fixtures]([Fixture ID])
go[/code]Also, if you care, you might want to look at the ISO-11179 standard for naming conventions.

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-03-05 : 15:18:50

Presenting the work of others as your own is academic fraud and we need to get you kicked out of school for it.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources