-1

Create a table, and can not make foreign keys, as foreign keys need to be referencing unique values - as far as I am aware. However on phpmyadmin, you can insert a row to the indexes and then reference it as a foreign key.

I am currently using SQL Server Management Studio and can't reference a foreign key.

My tables are 

create table gameEvents(
event id int,
eventtext varchar(500),
eventLocation varchar(150),
eventFeedback varchar(300),
choiceOne varchar(100),
choiceTwo varchar(100),
eventDifficulty int,
event Result int
)

Create table gameChoice(
eventDifficulty int,
eventResult int,
ChoiceOneR int,
ChoiceOneV int,
ChoiceTwoR int,
ChoiceTwoV int
)

I am trying to reference eventDifficulty and eventResult (gameChoice) to gameEvents with the same column names.

The code used

Alter table gameEvents ADD CONSTRAINT FK_difficulty FOREIGN KEY (eventDifficulty) REFERENCES gameChoice(eventDifficulty);

(Code was manually typed incase there is a syntax error)

Error Recieved - There are no primary or candidate keys in the referenced table 'gameChoice' that match the referencing column list in the foreign key 'FK_difficulty'

I understand the error. However I dont see any other way of making these foreign keys link, other than using phpmyadmin...is there a way to make these foreign keys on SQL Server Managment Studio

HARV mackie
  • 123
  • 1
  • 13

1 Answers1

1

You can create a foreign key reference to another table without a primary key constraint. However, to maintain referential integrity, you will need to create a unique key constraint on the EventDifficulty column on the GameChoice table.

You can try the following script for your alter statement.

Alter table gameChoice ADD CONSTRAINT UK_difficulty UNIQUE (eventDifficulty);
Alter table gameEvents ADD CONSTRAINT FK_difficulty FOREIGN KEY (eventDifficulty) REFERENCES gameChoice(eventDifficulty);
Abayomi
  • 102
  • 9